Figure how to add to a month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

This is what I am trying to do in Excel. Hope one of you gurus can help me out. This is what I am trying to do.

a1 = Month i.e. 04/06/04
b1 = Desired change in date, like add 5 months to the date above.
C1 = Changed date

The reason that I am trying to do this is, I am trying to automate something that I am trying to do in Excel, and I am stuck at this point.
 
Christopher said:
Hello All,

This is what I am trying to do in Excel. Hope one of you gurus can help
Click to expand...
me out. This is what I am trying to do.
a1 = Month i.e. 04/06/04
b1 = Desired change in date, like add 5 months to the date above.
C1 = Changed date

The reason that I am trying to do this is, I am trying to automate
Click to expand...
something that I am trying to do in Excel, and I am stuck at this point.

The simple answer is
=DATE(YEAR(A1),MONTH(A1)+5,DAY(A1))

Not all months are the same length, though. So, for example, adding 5 months
to 30 Sep 2003 gives the result 1 Mar 2004. If this sort of behaviour isn't
acceptable, define your requirements exactly and repost.
 
If you have the Analysis Toolpak installed, using Edate
would also be a possibility.
-----Original Message-----
Hello All,

This is what I am trying to do in Excel. Hope one of
Click to expand...
you gurus can help me out. This is what I am trying to
do.
a1 = Month i.e. 04/06/04
b1 = Desired change in date, like add 5 months to the date above.
C1 = Changed date

The reason that I am trying to do this is, I am trying
Click to expand...
to automate something that I am trying to do in Excel,
and I am stuck at this point.
 
If you want to keep the number of months to add in B1, a
slight modification to Paul's formula would be:

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

This would be in C1.
 
Hi Chris!

The solution to the addition of months where the day of month (DOM)
can be >=29:
=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2
,0))))
Substitute A1 and $A$1 by the cell reference that contains your base
date.

In your case of wanting to add 5 months the solution would be:
=DATE(YEAR(A1),MONTH(A1)+5,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+6
,0))))

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))))
Where AddMons is the number of months to be added and A1 contains the
base date.

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



These formulas are designed for copying down or across (hence absolute
reference to A1). This can be achieved with EDATE but that depends
upon Analysis ToolPak being installed and selected as an Addin. It can
also be achieved by using a manual Edit > Fill > Series.



"Straight" addition of months is not really wrong! It's just a case of
definition. In the above formula the definition used is to adopt the
last day of the month if the base DOM is greater than the last DOM.


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