Thanks:  0
Likes:  0

# Thread: Adding 1 month at a time to a date list

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

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

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

4. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•