Increase a date by 1 month

  • Thread starter Thread starter KRK
  • Start date Start date
K

KRK

Hello,

I have a date in in one cell, eg 22/04/08 and I want the next cells to be
one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a
month varies so I cant just add 30.

Can anyone help & advise please

Thanks

KK
 
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

But do think of what you want the result to be in cases like Jan 31.
 
KRK said:
I have a date in in one cell, eg 22/04/08 and I
want the next cells to be one month higher, ie
22/05/08 , 22/06/08 etc etc. The number of days in
a month varies so I cant just add 30.
Can anyone help & advise please
Click to expand...

=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1,1))

If you do not want to rely on the Analysis ToolPak (for EOMONTH), then:

=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),
DATE(YEAR(A1),2+MONTH(A1),0))
 
PS....

I forgot to mention that you might need to explicitly select the Date
format.

Also, if you enter two such dates a month apart, you can select the two
cells and drag them down (or across if the two cells are in a row), and
Excel will effectively do this computation automagically. However, what you
get are constants, not formulas. So if you change the first two dates
later, you will have to repeat the drag operation, although double-clicking
on the drag handle might work for you then.


----- original message -----
 
=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1,1))

This will do the same thing:

=EDATE(A1,1)
 
T. Valko said:
This will do the same thing:
=EDATE(A1,1)
Click to expand...

Well, duh! But I get paid by the keystroke :-).

Seriously, I think we both made the same mistake. Consider that A1 is
1/31/2008, and we put =EDATE(A1,1) into A2 and copy down. A2 will be
2/29/2008 (good), and A3 and all subsequent dates will be m/29/2008, until
after 1/2009, when all subsequent dates will be m/28/2yyy (oops!).

I think the correct formula starting in A2 and copied down is:

=EDATE($A$1,ROW(1:1))
 
Back
Top