Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am a novice wtih Excel formulas and I want to know if Excel can calculate months so that if you start off with a specific date, you can get that same date say three or six months out? For example, if my starting date is January 15, 2009, what would be the formula for 3 months out to get April 15, 2009 or if I started with January 15, 2009 and want six months out, what would the formula be to get July 15, 2009? I am trying to create a template that will do these types of calculations. I have done it before with days out and that works great, but I was just wondering if you can get specific dates as well. Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you're using Excel 2003 or before, you can activate the Analysis ToolPak add-in by ticking its check box in Tools | Add-Ins. This makes several new functions available, among which EDATE. In Excel 2007, these functions are available by default.

    Let's say that A1 contains a date such as January 15, 2009, and that B1 contains a number such as 3 or 6.
    The formula =EDATE(A1,B1) will return the date the specified number of months after the date in A1.
    If you specify a negative number in B1, the formula will return the date the specified number of months before the date in A1.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='HansV' post='779986' date='15-Jun-2009 23:52']If you're using Excel 2003 or before, you can activate the Analysis ToolPak add-in by ticking its check box in Tools | Add-Ins. This makes several new functions available, among which EDATE. In Excel 2007, these functions are available by default.

    Let's say that A1 contains a date such as January 15, 2009, and that B1 contains a number such as 3 or 6.
    The formula =EDATE(A1,B1) will return the date the specified number of months after the date in A1.
    If you specify a negative number in B1, the formula will return the date the specified number of months before the date in A1.[/quote]
    I am having difficulty understanding how this function works. Would 3 months after January 31st return the same result as 3 months after February 1st?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    With 30-Jan-2009 or 31-Jan-2009 in A1, the formula =EDATE(A1,3) returns 30-Apr-2009.
    With 01-Feb-2009 in A1, the formula returns 01-May-2009.

    EDATE tries to find the "same" day of the month the specified number of months in the future or past. If the "same" day doesn't exist because the target month has fewer days than the source month, the last day of the target month is returned. See screenshot:

    [attachment=84311:x.png]
    Attached Images Attached Images
    • File Type: png x.png (2.2 KB, 1 views)

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='HansV' post='780035' date='16-Jun-2009 10:07']EDATE tries to find the "same" day of the month the specified number of months in the future or past. If the "same" day doesn't exist because the target month has fewer days than the source month, the last day of the target month is returned. See screenshot:[/quote]
    Thanks, that makes sense.

  6. #6
    Lounger
    Join Date
    Mar 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your assistance and explanation. The Edate worked very well.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='Giulia' post='780154' date='17-Jun-2009 05:35'] Thank you for your assistance and explanation. The Edate worked very well.[/quote]
    Hi Giulia,

    If other people will be using the workbook, it's probably not a good idea to rely on them having the Analysis Toolpack enabled. A standard formula you could use is:
    =MIN(DATE(YEAR(A1),MONTH(A1)+3+{0,1},DAY(A1)*{1,0} ))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Lounger
    Join Date
    Mar 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='macropod' post='780200' date='17-Jun-2009 00:25']Hi Giulia,

    If other people will be using the workbook, it's probably not a good idea to rely on them having the Analysis Toolpack enabled. A standard formula you could use is:
    =MIN(DATE(YEAR(A1),MONTH(A1)+3+{0,1},DAY(A1)*{1,0} ))[/quote]


    Thank you for the suggestion. I had thought about that, so I checked with our IT department to see if it was already enabled and they assured me that it was. The EDate seems a bit simpler than the standard forumula.

Posting Permissions

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