how to calculate x months from today

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

Hi,

Try

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


date in a1 and months to add in b1

Mike

"Gene" wrote:

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

Months are such a terrible unit to perform math with. As an example, if you
add 6 months to the date August 31, 2009, what date do you expect to have
returned to you?

--
Rick (MVP - Excel)


"Gene" wrote in message
...
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" wrote:
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))

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?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Months are such a terrible unit to perform math with. As an example, if
you add 6 months to the date August 31, 2009, what date do you expect to
have returned to you?

--
Rick (MVP - Excel)


"Gene" wrote in message
...
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

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

"Mike H" wrote in message
...
Hi,

Try

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


date in a1 and months to add in b1

Mike

"Gene" wrote:

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

On Tue, 16 Jun 2009 13:54:54 -0700, "JoeU2004" wrote:

"Gene" wrote:
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))


Same algorithm, a bit shorter:

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

--ron