Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    Atlanta, Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Activate the Analysis ToolPak add-in in Tools | Add-Ins...
    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. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    New Lounger
    Join Date
    Oct 2003
    Location
    Atlanta, Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •