Advancing by One Month

[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)?

 

[Don Guillett]

Post your equations

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/datearith.htm

 

[Ron Rosenfeld]

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

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

 

[Guest]

Wow, what a resource. Thanks.

-----Original Message-----
Hi
see:
http://www.cpearson.com/excel/datearith.htm

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[gjlinker]

Hi,

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.

Find out about LITlib at www.oraxcel.com/projects/litlib

Best regards, Gerrit-Jan Linker
Linker IT Software Limited
www.oraxcel.co

 

[Gerrit-Jan Linker]

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

 

[Harlan Grove]

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.

....

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

 

[Don Guillett]

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