Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,609
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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