Add months to date

[Melany]

I'm sure there is a simple solution, but I'm overlooking
it. I need to add 35 months to a date (not today() ) and
get the new date. If my date in cell A1 is 21 Oct 98, in
A2 I need the sum of A1 + 35 months.

 

[Peo Sjoblom]

One way

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

 

[Mike]

=A1+DATE(0,35,0)

.... as the date function follows the format Date(year,months,days)


Mike

 

[John Chow]

DATE(TEXT(A1,"yyyy")+3,TEXT(A1,"mm")-1,TEXT(A1,"dd")-1)

John

 

[Paul]

I'm sure there is a simple solution, but I'm overlooking
it. I need to add 35 months to a date (not today() ) and
get the new date. If my date in cell A1 is 21 Oct 98, in
A2 I need the sum of A1 + 35 months.

The simple answer is
=DATE(YEAR(A1),MONTH(A1)+35,DAY(A1))
With this formula:
21 Oct 98 ==> 21 Sep 01
30 Oct 98 ==> 30 Sep 01

However, the length of a month varies. If the resulting date doesn't exist
(e.g. 31 Sep or 30 Feb), you get an 'equivalent' date early in the next
month. So:
31 Oct 98 ==> 1 Oct 01
30 Mar 98 ==> 2 Mar 01

If you want to restrict the date to the last day of the month in such
circumstances, use this array-formula (entered with CTRL+SHIFT+ENTER rather
than just ENTER):
=MIN(DATE(YEAR(A1),MONTH(A1)+1+{1,0},DAY(A1)*{0,1}))
With this formula:
31 Oct 98 ==> 30 Sep 01
30 Mar 98 ==> 28 Feb 01

 

[Steve]

12 months = 365, 365 divided by 12 x 3
so +a1+1064.583
I think

 

[Ron Rosenfeld]

I'm sure there is a simple solution, but I'm overlooking
it. I need to add 35 months to a date (not today() ) and
get the new date. If my date in cell A1 is 21 Oct 98, in
A2 I need the sum of A1 + 35 months.

Also: =EDATE(A1,35)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.


--ron

 

[msnews.microsoft.com]

Ron ...

How would I use this function to add days?

 

[Norman Harker]

Hi!

You can't add days with EDATE.

e.g.
=A1+25

or if compiling a date you might have something like:

=DATE(YEAR(A1)+1,MONTH(B1),DAY(C1)+25)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

Ron ...

How would I use this function to add days?

Well, EDATE can only be used to add months.

To add days, just add 1 for each day.

In other words, with

A1: Start Date
A2: Number of days to add
A3: =A1+A2


--ron

 

[msnews.microsoft.com]

Well, EDATE can only be used to add months.

To add days, just add 1 for each day.

In other words, with

A1: Start Date
A2: Number of days to add
A3: =A1+A2


--ron

 

[msnews.microsoft.com]

I was afraid it would be that simple .. in fact, that is what I had done ...
what is the actual description of EDATE, anyway?

 

[Norman Harker]

Hi mlibby!

From my Function Lists but easily obtained from Help:

EDATE
Source: Analysis ToolPak
Description:
Returns the Excel date / time serial number of the date that is the
indicated number of months before or after the specified number of
months from the start_date
Syntax:
=EDATE(start_date,months)
Start_date:
The first date as an acceptable date in inverted commas (discouraged)
or as a serial number or formula / function that returns a date serial
number.
Months:
The number of months before or after start_date
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.