Explanation
In this example, the goal is to calculate the number of months between two valid Excel dates. This is a curiously tricky problem in Excel because the number of days in a month varies, and the rules about how a whole month might be calculated are not obvious. In addition, there is not a modern Excel function dedicated to the task of calculating months between dates. I have no idea why. The best tool for the job is the mysterious DATEDIF function, which is kind of a black sheep in the Excel family.
The DATEDIF function
The solutions described below are based primarily on the DATEDIF function. DATEDIF (date + dif) is designed to calculate the difference between a start date and an end date in years, months, or days. DATEDIF takes 3 arguments: start_date, end_date, and unit and the generic syntax looks like this:
=DATEDIF(start_date,end_date,unit)
For this problem, the start dates come from column B and the end dates come from column C. For unit, we need to supply "m", because we want to calculate months between dates. See this page on the DATEDIF function for more information about the options available.
Calculate complete whole months
In the worksheet shown below, we are using the DATEDIF function to calculate complete whole months between a start date and an end date. The start dates come from column B and the end dates come from column C. The formula in D5, copied down, is:
=DATEDIF(B5,C5,"m")
As the formula is copied down, DATEDIF returns whole months between the start date in column B and the end date in column C. Notice that the first four results in D5:D8 are exact multiples of months, but the result in D9 has been rounded down to 5 because we are 1 day short of 6 months. Also notice that results in D13:D16 are a bit quirky, due to how DATEDIF handles end-of-month dates. For example, when the days match, the result is an exact multiple of months, as expected. However, when the days don't match, the results can be unexpected. For example, with a start date of July 31 and an end date of September 30, the result is 1, though most people would count this as 2 months. One workaround to this problem is described below.
Calculate nearest whole months
When calculating months between two dates, DATEDIF always rounds months down to the last complete number of months. This means DATEDIF rounds a 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 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 also 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:
Calculate decimal months
As shown above, DATEDIF only calculates 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 to 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 to calculate months touched by dates
In some cases, you may want to count how many months are "touched" by a given date range. The generic formula for this calculation looks like this:
=(YEAR(C5)-YEAR(B5))*12+MONTH(C5)-MONTH(B5)+1
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 subtracts the start month from the end month and adds 1 to calculate the remaining months:
MONTH(end)-MONTH(start)+1 // remaining months
Notice this calculation means that any month difference will result in a positive number because the day of the 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 be included in the result. You can see this effect if you compare results from the original DATEDIF formula in column E to results from the alternative formula in column F.
Related formulas
Get days between dates
Get workdays between dates
Get days, months, and years between dates
Related functions
DATEDIF Function
The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work...
YEARFRAC Function
The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC to do things like calculate age with a birthdate....
MONTH Function
The Excel MONTH function extracts the month from a given date as a number between 1 and 12. You can use the MONTH function to extract a month number from a date into a cell or to feed a month number into another function like the DATE function....
YEAR Function
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.
