# Thread: Months Before a Date (2002)

1. ## 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.

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

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

#### Posting Permissions

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