# Thread: Creating a consecutive date list (Excel 2003 SP1)

1. ## Creating a consecutive date list (Excel 2003 SP1)

Hi,

I am trying to come up with a formula that will allow me to automatically change a list of consecutive dates by changing the start date. For example, if I have column A formated so that all cells are dates, and cell A1 is my start date, Sep. 1, 06, what formula do I type into cell B1, that will give me the next sequential month, Oct 1, 06? If I use the formula =(A1+1), it just advances the date by one day. Yes, I can use =(A1+30), but if I copy and paste that formula through the rest of the column, any month that has 31 days won't calculate properly...i.e. my list will have two October dates in a row. I know this happens because Excel sees all dates as a normal number, but I'm hoping that there is a way I can use some variation of the Date function and a formula to solve this problem. Any help or comments would appreciated.

Thanks,
Jeff

2. ## Re: Creating a consecutive date list (Excel 2003 SP1)

This makes the EDATE function available. Syntax: =EDATE(date,number_of_months) will calculate the date number_of_months after the specified date (if number_of_months is negative, it's months before the specified date). Example:

=EDATE(A1,1)

3. ## Re: Creating a consecutive date list (Excel 2003 SP1)

Hi there,

Welcome to Woody's lounge.

If you have your date in A1, putting the following formula in Cell A2 (and then dragging the formula down) will put the 1st of each consecutive month into the cells

=((EOMONTH(A1,0))+1

(where A1 is your cell reference)

If you want the end of of each month simply use

=(EOMONTH(A1,1)

Regards

Robert

4. ## Re: Creating a consecutive date list (Excel 2003 SP1)

Hi Robert,

EOMONTH (which requires that the Analysis ToolPak has been installed too) works very well if you need the last or the first day of a month, but it's less convenient for arbitrary start dates. EDATE was designed for that purpose.

5. ## Re: Creating a consecutive date list (Excel 2003 SP1)

If you prefer not to install the Analysis ToolPak, the formula becomes more complicated because of the reason you mentioned:

=IF(DAY(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))<DAY(A1 ),DATE(YEAR(A1),MONTH(A1)+2,0),DATE(YEAR(A1),MONTH (A1)+1,DAY(A1)))

6. ## Re: Creating a consecutive date list (Excel 2003 SP1)

Thank you everyone. This was very helpful. Have a good weekend.

7. ## Re: Creating a consecutive date list (Excel 2003 SP1)

Thanks for correcting my ommision, Hans.
I've had the analysis pack installed for so long now, I forgot about that !

Robert

#### Posting Permissions

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