Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    Phoenx, Arizona, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate date range (Excel 2000)

    How do I calculate date ranges? (The "help" answers were not very clear.) For example: I have the date (10/21/03) entered and I want the next column to show a date 7 years from that date (10/21/10). What formula should I use?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculate date range (Excel 2000)

    If your date is in A1 you can use the formula:

    =DATE(YEAR(A1)+7,MONTH(A1), DAY(A1))

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate date range (Excel 2000)

    Say that you have a date in cell A1.

    To get the date 7 days later: =A1+7 or =DATE(YEAR(A1),MONTH(A1),DAY(A1)+7)

    To get the date 7 months later: =DATE(YEAR(A1),MONTH(A1)+7,DAY(A1))

    To get the date 7 years later: =DATE(YEAR(A1)+7,MONTH(A1),DAY(A1))

    Excel automatically adjusts for "overflow" in the day or month.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate date range (Excel 2000)

    Two of my favorite functions that were included in Excel 97's Analysis Toolpak are EDATE and EOMONTH. These may be part of Excel 2000's core set of functions. =EDATE(date,7) would give you the same date 7 months later. =EOMONTH(date,7) would move you ahead seven months and give you the last day of that month. EOMONTH is particularly useful for setting up a series of month end dates.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate date range (Excel 2000)

    EDATE and EOMONTH are still part of the Analysis Toolpak in Excel 2002.

    There is no equivalent EYEAR function. To get the date seven years from the date in A1 (as in the original question in this thread), one can use =EDATE(A1,7*12)

Posting Permissions

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