date & time formula

[dalley20]

If I have two columns, with a specific date in the first. In the secon
column I need the date from the first column plus one month. If it i
January 15th in the first column, i need it to be February 15th in th
second column. If though, for instance It is January 31st, I would nee
it to say February 28th. I also have to account for leap years. Is thi
possible? It would look something like this....

Column A Column B

26/08/04 26/09/04
30/04/04 30/05/04
31/01/04 29/02/04
31/01/03 28/02/03

Thanks in advance!!!!

Dalle

 

[Frank Kabel]

Hi
try the following:
=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0)
)))

 

[Bob Umlas]

This is very tough to do without a list of "exceptions" or rules. For
example, if Jan 31 becomes Feb 28, what happens to Jan 30 or Jan 29? Since
Jan 30 is 1 day before end of the next month, does it become Feb 27 to also
be 1 day before end of next month?
Does Feb 28 become Mar 31? Or does it become Mar 28? Or if leapyear, does
Feb 29 become Mar 29 or 31?
If Aug 30 becomes Sep 30, what happens to Aug 31?

 

[gaftalik]

Hi Dalley!

Try this formula :
=DATE(YEAR(A1);MONTH(A1)+1;DAY(A1))
where A1 is the date you want to add the month to
+1 is the nbr of month you need to add

Drag this formula to all needed areas
Re leap year , excel will solve it automatically.

Good luck !
Gaftali

 

[gaftalik]

Sorry Dalley,

I think this is the formula you need :
=DATE(YEAR(A1);MONTH(A1)+1;MIN(DAY(A1);DAY(DATE(YEAR(A1);MONTH(A1)+2;0))))

Hope it will be solved!

Gaftali