Adding 'x' months to a date

  • Thread starter Thread starter Eileen
  • Start date Start date
E

Eileen

I have a column with dates in it and a column
with 'intervals' in it, eg 3,6,9 (mths).
In the third column I need the result of the original date
plus the number of months in the 2nd column.
eg, 01/02/2003 + 3 mths = 01/05/2003
How do I do this?
 
=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))

Please note that 2003/11/30 + 3mths becomes 2004/3/1, not 2004/2/29.
 
Hi Eileen!

Here's a general formula for adding or subtracting months from a given
date:

Substitute A1 and $A$1 by the cell reference that contains your base
date.

If you want a "general" solution that works for creating any series of
evenly spaced months:

=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH
(A1)+AddMons+1,0))))
Substitute A1 and $A$1 by the cell reference that contains your base
date.
Where AddMons is the number of months to be added or subtracted



And here's an alternative that does the same by Peter Dorigo:



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


Where the day of the month >=29 the algorithm used is to use the base
date day of month where it exists and to use the last day of the month
if it does not.

A third alternative for creating a series of dates is:

=EDATE($A$1,(ROW(A2)-ROW($A$1))*AddMons) '[Adds number specified in
named cell AddMons]

If our requirement is for dates to be across the page, then substitute
ROW by COLUMN. In most cases I would not use $A$1 but would use a
named cell e.g. "BaseDate" and A2 would be replaced by the address of
the cell adjacent to "BaseDate" and is the second in the series of
dates.

Whatever method is used, it is essential to lock the day to the base
date. Otherwise you can get some unusual series.

--
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.
 
One way:

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

or, if you have the Analysis Toolpak Addin loaded (Tools/Addins...):


C1: =EDATE(A1,B1)
 
JE McGimpsey said:
One way:

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

or, if you have the Analysis Toolpak Addin loaded (Tools/Addins...):


C1: =EDATE(A1,B1)
Click to expand...

I had not come across EDATE before but you have to be careful with it
and know what to expect. When I tried EDATE(A1,1) I got the following.

1/28/2004 2/28/2004
1/29/2004 2/29/2004
1/30/2004 2/29/2004
1/31/2004 2/29/2004


I guess the answers are justifiable for billing purposes but getting
the last three answers identical looks a bit strange to an
non-accountant!
 
Hi James!

All that EDATE is "saying" is that if the day number does not exist in
the target month, take the last day.

To many:

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

Looks equally strange.

Try that with the following series:

30-Jan-04 01-03-2004
31-Jan-04 02-03-2004
01-Feb-04 01-03-2004
02-Feb-04 02-03-2004
03-Feb-04 03-03-2004
04-Feb-04 04-03-2004
05-Feb-04 05-03-2004
06-Feb-04 06-03-2004
07-Feb-04 07-03-2004


That means that someone billed on 31-Jan-2004 pays later than someone
billed on 1-Feb-2004.

It's a question of defining a month and deciding what you want to do.

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