Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Date Formula (Excel 97)

    I need help with an Excel Formula. I have a date which shows 15 Mar 02. The cell underneath it I would like to display 6 months after the 15 Mar 02. The day 15 must stay the same. So Ideally it should display:

    15 Mar 02
    15 Sep 02
    15 Mar 03
    15 Sep 03

    However, the first cell could have the date 15 Jan 02 and the cells which follows should show the correct dates six monthly.

    Any help would be appreciated.

    Regards

    EnglishRose

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Date Formula (Excel 97)

    Let A2 house 15 Mar 02, the initial date.

    In A3 enter and copy down:

    =EDATE(A2,6)

    which requires that the Analysis Toolpak is activated (via Tools|Add-ins)

    or

    =DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))

    Aladin
    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Date Formula (Excel 97)

    Thanks for that it works really well <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel Date Formula (Excel 97)

    Hi englishrose,

    What do you want Excel to do if you're source date is the 31st and the target month has 28, 29 or 30 days? Likewise if your starting from 29 or 30 September, which gives 29 February (OK in a leap year) or 30 February? Should these situations be interpreted as falling on the last day of the target month, or rolling over into the next month?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Date Formula (Excel 97)

    The source date won't change it will stay at the 15th of each month.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel Date Formula (Excel 97)

    OK, but be aware that the solutions you've been given might not give the results you want if you use them in such situations.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Date Formula (Excel 97)

    Thanks for the advise

    English Rose

Posting Permissions

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