how to calculate x months from today

[Gene]

Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate
the date which is 6 months from now?
The issue I'm having is that some months are 29, 30, 31 days.

The "date" call value may change and the "x" months period could also change.

Hope this is clear.
THANKS!
Gene

 

[JoeU2004]

Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate
the date which is 6 months from now?
The issue I'm having is that some months are 29, 30, 31 days.

Try EDATE(A1,A2), where A1 is the original date and A2 is the number of
months. You may need to explicitly format the cell as Date or whatever
Custom date format you wish.

If you get a #NAME error, see the EDATE help page.

If you are unable or unwilling to install the Analysis ToolPak, it is
possible to do this with EDATE. Try:

=min(date(year(A1),A2+month(A1),day(A1)), date(year(A1),1+A2+month(A1),0))

 

[T. Valko]

Maybe better illustrated using this example:

A1 = 1/31/2009

What date should be returned after adding 1 month to the date in cell A1?

There is no 2/31/2009 so what date should be returned?

 

[Ashish Mathur]

Hi,

With the date as 1/31/2009 and B1 holding 1, your formula would return
3/3/2009 whereas the EDATE function would return 2/28/2009. I wonder which
one is correct. In your formula, B1 is interpreted as 31 days and therefore
the answer is 3/3/2009.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

Try EDATE(A1,A2), where A1 is the original date and A2 is the number of
months. You may need to explicitly format the cell as Date or whatever
Custom date format you wish.

If you get a #NAME error, see the EDATE help page.

If you are unable or unwilling to install the Analysis ToolPak, it is
possible to do this with EDATE. Try:

=min(date(year(A1),A2+month(A1),day(A1)), date(year(A1),1+A2+month(A1),0))

Same algorithm, a bit shorter:

=MIN(DATE(YEAR(A1),MONTH(A1)+A2+{1,0},DAY(A1)*{0,1}))

--ron