Increase a date by 1 month
 

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

Increase a date by 1 month
 

=DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KRK" wrote in message
...
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

Increase a date by 1 month
 

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

But do think of what you want the result to be in cases like Jan 31.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"KRK" wrote in message
...
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

Increase a date by 1 month
 

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

Regards,
Alan.
"KRK" wrote in message
...
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

Increase a date by 1 month
 

"KRK" wrote:
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

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

Increase a date by 1 month
 

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

"JoeU2004" wrote in message
...
"KRK" wrote:
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

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

Increase a date by 1 month
 

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

This will do the same thing:

=EDATE(A1,1)

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"KRK" wrote:
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

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

Increase a date by 1 month
 

"T. Valko" wrote:
=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A 1,1))

This will do the same thing:
=EDATE(A1,1)

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

Increase a date by 1 month- Thanks
 

Thanks for the help

KK


"KRK" wrote in message
...
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