Add months to date

  • Thread starter Thread starter Melany
  • Start date Start date
M

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

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

Also: =EDATE(A1,35)

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


--ron
 
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 ...

How would I use this function to add days?
Click to expand...

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
 
Ron Rosenfeld said:
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
Click to expand...
 
I was afraid it would be that simple .. in fact, that is what I had done ...
what is the actual description of EDATE, anyway?
 
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.
 
Back
Top