Forum Discussion
Want to show specified text based on month in selected cell in excel
Hello everyone,
I'm stuck with the next problem:
I am creating something like resource list of parts in maintenance. My goal is to show text in a cell based on year quartals (jan, feb and mar are the first quartal, apr, may and jun second, etc).
I have parts with 9 years of lifetime.
I have first column with a names of parts, second column shows years of lifetime, third is showing quartal of the year when the part is manufactured, and fourth needs to show me date when the lifetime of the part ends (for example: part is manufacured "1Q/18" then the fourth column needs to show date of expiration of that part in next format "31.03.2027.").
My first idea was to create an "IF" formula which will do this: if the first part of the cell is "1Q" then in next cell show date "31.03". If the first part of the cell is "2Q" then show date "30.06". I want the day and the month to depend on quartal of the year (first part of the cell) and year must sum da year of manufacturing "/18" (18 says that part is manufactured in 2018.) plus year of lifetime of part. In this specific case 2018(/18)+9 years (lifetime is 9 year) = 2027. So, if i enter in a cell "1Q/18" the next cell needs to show me "31.03.2027.".
If anyone can help, i will be gratefull.
Thanks a lot.
For this setup
that could be
=EDATE( EOMONTH( DATE( 2000+RIGHT(C3,2), LEFT(C3)*3, 1), 0), LEFT(B3, SEARCH(" ", B3)-1)*12)
For this setup
that could be
=EDATE( EOMONTH( DATE( 2000+RIGHT(C3,2), LEFT(C3)*3, 1), 0), LEFT(B3, SEARCH(" ", B3)-1)*12)