In Excel, to get the count of months between two dates the best way is to use the DATEDIF function. In this function, you need to specify the start date and the end date. After that, you also need to specify the unit in which you want to get the result.
In the following example, we have two dates in cells A2 and B2.
Formula to Get Months Between Two Dates (DATEDIF)
- First, in cell C2, enter the DATEDIF function.
- After that, in the first argument refer to cell A2 where you have the starting date.
- Next, in the second argument refer to the B2 where you have the ending date.
- Now, in the third argument enter “m”.
- In the end, hit enter to get the result.
Here you can see, that you have got 11 in the result, which is correct as we have 11 months between March 2021 to Feb 2022.
As you have seen when you enter the DATEDIF function Excel doesn’t show the function, and you will not find this function in the list of functions in the list. You can learn more about DATEDIF from here.
You can also use DATEDIF in a way to show months and days separately but in a single cell.
In the above formula, we have used DATEDIF twice to get months and then days and then concatenate both of the formulas to get a complete result.
Months Between Two Dates using YEARFRAC
There might be a situation where we need to count the months between 2 dates, but we also need to consider the days that we have after the completed months. In that case, the best way is to use the YEARFRAC function.
In YEARFRAC, you need to specify the start date in the first argument and then you need to specify the ending date in the second argument. And after that, use the asterisk sign to multiply it by 12.
Now once you hit enter, you will get the total number of months that you have between these two dates as integers and days as decimals.
In the above example, we have the 1st March 2021 as the start date and 23 Feb 2022 as the end date. Now when I use YEARFRAC, it returns 11.73 where 11 is the number of months and .73 is the decimal value that represents the 23 days that we have in February month.
If you want to round the result, you can wrap this formula with the round function.
Using YEAR + MONTH Function to Get Month Between Two Dates
There’s one more method that you can use to get the count of the month between two dates. By combining YEAR and MONTH functions you can create the following formula that returns months.
To understand this function, you need to split it into two parts:
In the first part, where we have the year function, it takes the year of the end date and then the year of the start date and then multiplies it with the twelve. This part returns the number of months that you have between dates.
This part of the formula only makes a difference when you have dates with different years, both dates have the same years then this part will not make any difference to the calculation. As you can see we have 2021 and 2022 and the result that this part of the formula gives is 12.
In the second part, we are deducting the month of the end date from the month of the start date. And this part also ignores the days we have access.
So, when we deduct March (3) from Feb (2) we get -1 in the result.
After that, in the end, it deducts -1 from the 12 we get 11 which is the total number of months that we have in between both dates. If you ask me which method I like the most, I’d say DATEDIF which is simple to use and easy to understand by the user.
⇠ Back to Excel Formulas List