Date arithmetic: adding 1 month to prior end of month date

[Guest]

I'm using the =DATE(YEAR(B32),MONTH(B32)+1,DAY(B32)) where B32 is the prior
date. This works fine when the prior month's day is not the last day of the
month. If it is the last day, the results are a little goofy. For example,
adding one month to 6/30/06 produces 7/30/06 instead of 7/31/06. Adding one
month to 1/31/06produces 3/3/06 instead of 2/28/06.

Is there a way to add whole months to prior months when the prior month's
day is the last day of the month?

 

[SteveG]

manxman,

You could use,

=EOMONTH(A1,1)

Where A1 = your start date. You need the Analysis ToolPak installed t
use the EOMONTH function. Tools, AddIns, select Analysis ToolPak an
click OK.

HTH

Stev

 

[Guest]

That does it. Thanks very much.