# Thread: Calculating Months

1. 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. 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. [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. 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]

5. [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. Thank you for your assistance and explanation. The Edate worked very well.

7. [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} ))

8. [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
•