Note: DATEDIF automatically rounds down to the nearest whole month. To round up to the nearest month, see below.
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: 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: 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 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: 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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
