Adding 9 months to date

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

We're trying to take a date and add 9, 12 or 18 months to
the it. For instance 11/03/03 plus 9 months. It should
be the same day of the month - just 9 months later. Any
ideas?
 
Carol,

Try something like
=DATE(YEAR(A1),MONTH(A1)+9,DAY(A1))

where A1 contains your date.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
One way:

A1: 11/03/2003
A2: =DATE(YEAR($A$1),MONTH($A$1)+9,DAY($A$1)) ==> 08/03/2004
 
Carol,

Simplistically

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

There is a pitfall here though. Consider the date 31st May. Add 9 months to
this and Excel will return 2nd March the following year.Do you want this or
28th Feb? Assuming the latter, what happens if you than add on 3 more
months, should it revert to 31st again (difficult as you would need to
retain the original).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
will
 
Hi Bob,

That is an excellent point!

I wonder if something like the following would be helpful?

=DATE(YEAR($A$1),MONTH($A$1)+9,IF(DAY($A$1)>DAY(EOMONTH($A$1,9)),
DAY(EOMONTH($A$1,9)), DAY($A$1)))

You could assign a variable to the months to add and get

=DATE(YEAR($A$1),MONTH($A$1)+MonthsAdd,IF(DAY($A$1)>DAY(EOMONTH($A$1,MonthsA
dd)), DAY(EOMONTH($A$1,MonthsAdd)), DAY($A$1)))

Of course, you need the Tools | Options | Analysis Toolpack for the EOMONTH
function. But it still might help to address those tricky dates near the
end of the month?

Regards,
Kevin
 
...
...
I wonder if something like the following would be helpful?

=DATE(YEAR($A$1),MONTH($A$1)+9,IF(DAY($A$1)>DAY(EOMONTH($A$1,9)),
DAY(EOMONTH($A$1,9)), DAY($A$1)))

You could assign a variable to the months to add and get

=DATE(YEAR($A$1),MONTH($A$1)+MonthsAdd,IF(DAY($A$1)>DAY(EOMONTH($A$1,MonthsA
dd)), DAY(EOMONTH($A$1,MonthsAdd)), DAY($A$1)))

Of course, you need the Tools | Options | Analysis Toolpack for the EOMONTH
function. But it still might help to address those tricky dates near the
end of the month?
Click to expand...
...

First, it's Tools > Add-Ins..., then check the entry for the Analysis ToolPak
(note Microsoft's quirly spelling) in the Add-Ins dialog.

Not useful. If you're going to assume the presence of the Analysis ToolPak, why
not just use its EDATE function and be done with this? That is,

=EDATE($A$1,MonthsAdd)

If the goal is to recreate EDATE's functionality without any ATP functions, then

=DATE(YEAR($A$1),MONTH($A$1)+MonthsAdd,MIN(DAY($A$1),DAY(DATE(YEAR($A$1),
MONTH($A$1)+MonthsAdd+1,0))))
 
Harlan,

<< =EDATE($A$1,MonthsAdd) >>

I didn't know it existed.

Your solution appears to be the best one because a) it correctly treats 31
May + 9 months (and doesn't give 2 Mar 2004 as an answer), and b) it is
clean and simple.

Regards,
Kevin
 
Back
Top