Expiry Date calculation

[Arup C]

Hi everybody,
I want to calculate expiry date of a product which is expired after 9 months
or 12 months suppose the data is like following
Product Mfg. date Expires after (in months)
X Apr 07 9
Y Apr 07 12

It should result Dec 07 for X and Apr 08 for Y
Now is there any formula to help this out?

 

[Stephen]

Hi everybody,
I want to calculate expiry date of a product which is expired after 9
months
or 12 months suppose the data is like following
Product Mfg. date Expires after (in months)
X Apr 07 9
Y Apr 07 12

It should result Dec 07 for X and Apr 08 for Y
Now is there any formula to help this out?

Well, yes, if the "Mfg.date" is entered as a date (that may be formatted to
display month and year) rather than just text.

If so, and supposing your example data and headers occupy A1:C3, in D2 put
the formula
=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))
and copy this down (in your example, this is just to C3).

If not, your first job will be to change the text to proper dates. (In case
you don't know, if you're not concerned with the day of the month, just
enter all dates as the first of the month. You can then format them as "mmm
yy" to display them as in your example.)

 

[Kevin B]

Assuming that the Mfg. Date is in column B, the Expires After is in column C,
the following formula will produce the desired result:

=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))

It adds the number of months in column c to the month of the mfg. date and
produces the expiration date.

 

[Stefi]

If your Windows Short Date format is set to MMM yy then
=DATEVALUE(B2) shall recognize Apr 07 as an Excel date. Then follow Kevin's
instructions!
Regards,
Stefi


„Kevin B†ezt írta: