how to calculate x months from today

  • Thread starter Thread starter Gene
  • Start date Start date
G

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:)
 
Gene said:
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.
Click to expand...

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))
 
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?
 
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
 
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))
Click to expand...


Same algorithm, a bit shorter:

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

--ron
 
Back
Top