Advancing by One Month

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

Guest

Say I have an equation in Excel in B1 set to add 9 months
to whatever is typed in A1. The problem is that the
default seems to be that 1 month to Excel is about 30
units, so that 9 months would be 270. Unfortunately, this
means that if you type in 31-May-2003, for example, it
will bring up 25-Feb-2004 when it ought to be 31-Mar-2004.

Is there a way to set it up so that Excel will look at the
date typed, and simply increase the month portion of the
value 9 times (adding to the year if appropriate)?
 
Say I have an equation in Excel in B1 set to add 9 months
to whatever is typed in A1. The problem is that the
default seems to be that 1 month to Excel is about 30
units, so that 9 months would be 270. Unfortunately, this
means that if you type in 31-May-2003, for example, it
will bring up 25-Feb-2004 when it ought to be 31-Mar-2004.

Is there a way to set it up so that Excel will look at the
date typed, and simply increase the month portion of the
value 9 times (adding to the year if appropriate)?
Click to expand...


Look at the EDATE worksheet function.

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


--ron
 
I've written LITlib a library with Excel functions. I have supported
the DateAdd method with which you can achieve what you'd like to do:

=DateAdd(Interval, Number, Date )
=DateAdd( "m" , 9 , Now() )

This will add 9 months to the current date.

Have a look at LITlib at:
www.oraxcel.com/projects/litlib

Best regards, Gerrit-Jan Linker
Linker IT Software Limited
www.oraxcel.com/projects/litlib
 
gjlinker > said:
The LITlib worksheet functions library has a function to do this:

=DateAdd(Interval, Number, Date)

Example:

=DateAdd("m",9,now())

This will add 9 months to the current date.
Click to expand...
....

This is an example of a udf that's not strictly necessary, just a
convenience. The same result could be achieved with

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

(Actually, the day term would need an adjustment to deal with differing
number of days in months.) The ATP's EDATE function already handles months,
days are trivial, and years aren't too difficult (except adding years not a
multiple of 4 to 29-Feb-YYYY).
 
From a posting by Norman Harker
change addmons to 9 or use a cell named addmons

=DATE(YEAR(D1),MONTH(D1)+addmons,MIN(DAY($D$1),DAY(DATE(YEAR(D1),MONTH(D1)+a
ddmons+1,0))))
or
=MIN(DATE(YEAR(D1),MONTH(D1)+ addmons +{1,0},DAY($D$1)*{0,1}))
 
Back
Top