Number of months between two dates Excel 2010

In this example, the goal is to calculate whole months between two valid Excel dates. This is a slightly tricky problem in Excel because the number of days in any given month varies, so the rules about how a whole month might be calculated are not obvious. The solution described below uses the DATEDIF function. DATEDIF is a "compatibility" function that comes originally from Lotus 1-2-3. You can use DATEDIF in all current Excel versions, but you must enter the function manually – DATEDIF will not appear as a suggested function in the formula bar and Excel will not help you with function arguments. See this page on the DATEDIF function for more information about available arguments.

Complete whole months

In the example shown, we are calculating complete whole months between a start date and an end date with the DATEDIF function. DATEDIF takes 3 arguments: start_date, end_date, and unit. In this case, we want months, so we supply "m" for unit. In the example shown, the formula in D5, copied down, is:

=DATEDIF(B5,C5,"m")

The behavior of DATEDIF is automatic. The result is an integer that represents complete whole months between the start date in column B and the end date in column C. Notice that the result in rows 5, 6, and 7 is an exact multiple of months, but the result in D8 has been rounded down.

Also notice that results in rows 12 through 15 are a bit quirky, due to the way DATEDIF handles end of month dates. Basically, when the days in the start and end months match, the result is an exact multiple of months. However, when days don't match, results may be unexpected. For example with a start date of July 31 (end of month) and an end date of August 30 (end of month) the result is zero, though most people would count this as 1 month. One solution to this problem is described below.

Nearest whole month

When calculating months between two dates, DATEDIF always  rounds months down to the last complete number of months. This means DATEDIF rounds the result down even when it is very close to the next whole month. To calculate months to the nearest whole month, you can adjust the formula as shown below:

=DATEDIF(start_date,end_date+15,"m")

In this version of the formula, we add 15 days to the end date. This ensures that end dates occurring in the second half of the month are treated like dates in the following month, effectively rounding up the final result to the next even multiple. End dates that occur in the first half of the month increase by 15 days, but not enough to affect the normal result from DATEDIF. The screen below shows how the original DATEDIF formula compares to the modified version:

Decimal months

DATEDIF works only with whole months. To calculate a decimal number that represents the number of months between two dates, you can use the YEARFRAC function like this:

=YEARFRAC(start,end)*12

YEARFRAC returns fractional years between two dates, so the result is an approximate number of months between two dates, including fractional months returned as a decimal value. The screen below shows how the original DATEDIF formula compares the YEARFRAC version:

Keep in mind that the behavior of this formula depends on how YEARFRAC works. See this page for more information.

Alternative formula

In some cases, you may want to count how many months are "touched" by a given date range. The generic formula for this calculation is:

=(YEAR(end)-YEAR(start))*12+MONTH(end)-MONTH(start)

This formula uses the YEAR function and the MONTH function to work out a count in two parts. First, the months associated with a year change are calculated:

(YEAR(end)-YEAR(start))*12 // months due to year change

This code only affects the count if the start year and end year are different. If the start year and end year are the same, the result is zero.

Next, the formula simply subtracts the start month from the end month:

MONTH(end)-MONTH(start) // month change only

Notice this calculation means that any month difference will result in a positive number because the "day of month" is completely ignored.

Finally, the two parts of the formula are added together to get a total month count. The screen below shows the formula in action, compared to the original DATEDIF formula above:

As you would expect, this formula tends to increase the month count for a date range, since even a small portion of a month will increase the count. For example, in rows 8 and 10, the month count is increased even though only 1 day of the month overlaps the date range.