Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expiry Date calculation
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expiry Date calculation
"Arup C" wrote in message
... 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.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expiry Date calculation
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. -- Kevin Backmann "Arup C" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expiry Date calculation
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: 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. -- Kevin Backmann "Arup C" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate expiry date | New Users to Excel | |||
VBA For Expiry date | Excel Discussion (Misc queries) | |||
Expiry date | Excel Worksheet Functions | |||
Date Expiry function | Excel Discussion (Misc queries) | |||
Date of expiry - warning? | Excel Worksheet Functions |