Results 1 to 6 of 6
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Months Before a Date (2002)

    I have a formula for calculating a date N months after a certain date. I need to change it to calculate a date N months BEFORE a certain date, and I'm having trouble.

    I'm starting with this (from an Internet Excel help page):

    ==============================
    Note, however, that adding a month to a date may give you a result that you do not expect. For example, suppose A1 contains the date 31-Jan-2002. If you use the formula

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

    you will get the date 3-March-2002, because the "31st" day of February, 2002, is 3-March. The formula below will work around this issue, returning the last day of the next month if the date in A1 is a day that does not exist in the next month.

    =DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY(A1),DAY(DATE(YE AR(A1),MONTH(A1)+2,0))))

    For example, if A1 contains the date 31-Jan-2002, this formula will return 28-Feb-2002. It is important that you and your users understand what "one month later" means in the context of your workbook.
    ==============================

    The second formula works great, and shows that one month from 1/31/04 is 2/29/04.

    By changing the +1 to -1 and the +2 to -2, the formula ALMOST works in subtracting months. But 2/29/04 minus one month comes out to 1/29/04, and I want it to come out 1/31/04.

    I'm sure everything is wrapped up in the MIN and the -2, but I don't understand the formual perfectly, and I'm at a loss when trying to figure it out experimentally.

    This should be simple for SOMEBODY, but it's highly frustrating to ME.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Months Before a Date (2002)

    If 2/29/04 is in A12 then

    ==DATE(YEAR(A1),MONTH(A1)-<font color=red>0</font color=red>,1)-1

    should return Jan 31 2004.

    This works by subtracting 1 from the first day of the month after th edate you are looking for. This approach is only suitable if you are looking for month end dates. The figure in red is the number of months you want to subtract MINUS 1 ( 1 - 1 = 0 in this case).

    Hope that helps.

    Andrew

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

    Re: Months Before a Date (2002)

    1. If you check the Analysis Toolpak in Tools | Add-Ins..., you can use the EDATE function that does the same as the complicated formula you posted: if A1 contains 01/31/2004, the formula =EDATE(A1,1) will return 02/29/2004. And if A1 contains 02/29/2004, the formula =EDATE(A1,-1) returns 01/29/2004. Not what you want, but consistent with the formula you have.

    2. One month before 02/28/2004 is 01/28/2004, agreed? You want one month before 02/29/2004 to be 01/31/2004. If you really want it that way, I or someone else can probably come up with something, but it seems strange to me.

  4. #4
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Months Before a Date (2002)

    Hi,

    The idea of using EDATE is usable for any date except dates higher then 28. Since 28 trough 31 are possible numbers for month ends. I hope that you only want to use month end dates? If that is so in the Analysis Toolpack there is also the function EOMONTH. This goes forward or back the desired number of month.

    Goodluck Forbaty

  5. #5
    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: Months Before a Date (2002)

    How about this?

    =IF(DATE(YEAR(A1),MONTH(A1)+1,1)-1=A1,DATE(YEAR(A1),MONTH(A1),1)-1,EDATE(A1,-1))

    If the date in A1 is the last day of the month, it will take the last day of the previous month, otherwise it will take the month before using Edate function

    Steve

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Months Before a Date (2002)

    Thanks, guys! With all that you've posted, I'm able to do exactly what I wanted.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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