Adding 1 month at a time to a date list

[ad5184]

I need to add one month at a time to a date list but with the months being different number of days I can't just add a number (eg 30) to the date above. I need the list to read eg...1/1/02, 2/1/02, 3/1/02, etc

[Aladin Akyurek]

On 2002-04-10 11:56, ad5184 wrote:
I need to add one month at a time to a date list but with the months being different number of days I can't just add a number (eg 30) to the date above. I need the list to read eg...1/1/02, 2/1/02, 3/1/02, etc

Use

=EDATE(A1,1) [ requires the Analysis Toolpak, an add-in ]

or

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

[kmiles]

On 2002-04-10 11:56, ad5184 wrote:
I need to add one month at a time to a date list but with the months being different number of days I can't just add a number (eg 30) to the date above. I need the list to read eg...1/1/02, 2/1/02, 3/1/02, etc

type 1/1/02 in first cell, 2/1/02 in second cell, highlight both cells, then put cursor over crosshair at bottom right of last cell, click and drag - Excel will do the rest.

[IML]

This is just for fun (demented, I know)

The poor man's edate (will be wrong 3 days every four years).

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY(A1),VLOOKUP(MONTH(A1)+1,{13,31;2,28;3,31;4,30;5,31;6,30;7,31;8,31;9,30;10,31;11,30;12 ,31},2,0)))

Okay, here is the fix until, I believe the year 4000
=DATE(YEAR(B2),MONTH(B2)+1,MIN(DAY(B2),VLOOKUP(MONTH(B2)+1,{13,31;2,28;3,31;4,30;5,31;6,30;7,31;8,31;9,30;10,31;11,30;12 ,31},2,0))+((MONTH(B2)=1)*(MOD(YEAR(B2),4)=0)*(DAY(B2)>28)))

[ This Message was edited by: iml on 2002-04-10 12:20 ]