How to Translate Nr. of Months into a Future Date

[BSquared18]

Hi,

I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).

I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.

For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?

It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.

Thanks,
Bill

[teylyn]

Well, basically, you just need to add the number of months to the date in question.

So, for

A1 = date
B1 = months to be added (as per your description above, let's assume is 6)
C1 =date(year(a1),month(a1)+b1,day(a1))

The only thing you have to keep in mind that sometimes there will be seemingly funny results, e.g. if you add one month to 31-march with this formula, it will return 1-May, because there is no 31-April. There are ways around that, but the formula is more complicated. If you want the complicated solution, give me a yell.

cheers

[Paul]

Hi Bill,

If your starting date is in A1, and 6 is in B1, use:

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

[DonkeyOte]

As already outlined by Teylyn if the DAY is variable using DATE function can lead to erroneous results so my advice would be: if you are able, activate the Analysis ToolPak via Tools -> Addins and use the EDATE function

=EDATE(A1,B1)

(there are other useful date functions within this pack like EOMONTH, NETWORKDAYS, WORKDAY etc...)

To illustrate the difference, if A1 were 31/12/2009 and B1 were 2

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

would result in 3rd March 2010 whereas

=EDATE(A1,B1)

would result in 28th Feb 2009

ie EDATE will auto adjust to last day of the new month as and where required.

Obviously EDATE does not account for your decimal month addition (6.4)

If you want to do that you would need to make some very basic & fundamental decisions as to what 6.4 equates to..

If you assume 1= 30 days then you could simply opt for:

=A1+(B1*30)

[BSquared18]

Thanks for the help.

If your starting date is in A1, and 6 is in B1, use:

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

Suppose I just want to use whatever the current date on the computer is. Is there a way to do that computation automatically, or do I need to enter the date into a cell. It's no big deal to do the latter, but I was just curious whether the other way is possible.

Bill

[DonkeyOte]

see TODAY() worksheet function

[teylyn]

for completeness' sake: your ready-to-copy-and-paste solution:

Just replace A1 in the formula below with today()

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

so you get

=DATE(YEAR(TODAY()),MONTH(TODAY())+B1,DAY(TODAY()))

enjoy

[BSquared18]

Hi,

Long time since I started this thread. I revisited the formula =DATE(YEAR(TODAY()),MONTH(TODAY())+B1,DAY(TODAY())), trying to use it again. I noticed that it seems to work only for whole numbers. For example, if I enter the numeral 2, for 2 months, I get the correct date. If I enter 2 plus any decimal, such as 2.3, I get the same result. Is there a way to have the result sensitive to decimal portions of a month?

Thanks,
Bill

[daddylonglegs]

You can use Donkeyote's suggestion, e.g.

=TODAY()+B1*30

where 30 days is an approximation of a month, or arguably more accurate you can use your current formula with an additional part to handle the fractional months, e.g.

=DATE(YEAR(TODAY()),MONTH(TODAY())+B1,DAY(TODAY()))+MOD(B1,1)*30

[BSquared18]

Thanks, Daddy Longlegs! It works great now.

You may have helped me get over my fear of spiders.

Bill