Are you trying to calculate the number of months between two dates in Excel?
Whether you’re trying to track an important project timeline or measure customer retention over time, having an easy and accurate way to find the duration between dates is essential.
However, managing your data and time-sensitive calculations can be a challenging task.
This comprehensive guide offers step-by-step instructions, sample calculations, and formulas that explain how to use Excel functions efficiently to calculate the number of months between two dates easily.
Calculate the Number of Months Between Two Dates Using the DATEDIF Function
The easiest way to find the number of months between two dates is with the Date Difference or DATEDIF function.
This function is usually used to calculate the difference between days of two dates, and with little tweaks in its formula, you can use it to calculate the number of months.
Use the following syntax to calculate the number of months:
=DATEDIF(start_date, end_date,"M")
- start_date is the selected cell which contains the initial date
- end_date is the selected cell which contains the later date
Unlike all other formulas in Excel, when you type the DATEDIF function in a cell in Excel, you would not see any guidance or IntelliSense on what arguments it takes. You need to know the syntax to use the formula
Let us understand the DATEDIF function with an example.
Suppose you have a data set of dates as shown in the below image.
In this case, you can type the DATEDIF formula in E2.
The formula in this example will look like this:
=DATEDIF(A2, B2,"M")
Once you have typed the formula, press Enter.
The value displayed will be the number of months between the selected dates.
To implement the DATEDIF formula for the remaining dates, select the C2 cell and drag the Fill Handle till the desired cell (or copy the cell that has the formula and paste it on the remaining cells where you want the result).
This will give you the number of months for the remaining dates.
In the above formula, we have used A2 & B2, which contain the value of the initial and later date, respectively. Then we used “M” as the third argument.
Using “M” as the third argument tells Excel that we want to calculate the total number of completed months between the start date and the end date.
Additionally, if you are looking for more precise information, that gives the total number of months as well as days, you can apply the below formula:
=DATEDIF(start_date,end_date,"M")&"M "&DATEDIF(start_date,end_date,"MD")&"D"
If you use this formula for the above example, the DATEDIF function for calculating the number of months and days will be:
=DATEDIF(A2,B2,"M")&"M "&DATEDIF(A2,B2,"MD")&"D"
The output generated by Excel will be in the following manner:
In the above example, apart from calculating the total number of months between the two specified dates, we also wanted to know how many days there are after we have got the total number of completed months.
Using the code “MD” gives us the total number of days looking between the two dates when we have already counted the completed months.
You should note that the DATEDIF function excludes the start date while counting the number of months.
For example, if your state date is 01 March and your end date is 31 March, the DATEDIF function will count it as 0 months because, as per the DATEDIF function, only 30 days were covered in March (i.e., DATEDIF only counts the months that have elapsed)
Calculate the Number of Months Between Two Dates Using the YEARFRAC Function
Another method to calculate the number of months between two dates is by using the YEARFRAC function.
As the name suggests, the function is used to calculate the number of years that have passed during two dates.
However, by making some changes in its formula, you can also use the YEARFRAC to calculate the number of months between two dates.
You can use the below syntax to use the YEARFRAC function to calculate the number of months between two dates:
=YEARFRAC(start_date, end_date)*12
One thing to keep in mind while using the YEARFRAC function is that it will give you values in decimal in case a month has not elapsed between the two dates.
Let us apply the YEARFRAC in the earlier example.
Type the formula in C2. In this case, the formula will be:
=YEARFRAC(A2, B2)*12
Press Enter, and you will get the value of the number of months between two dates for the selected cells.
Select the C2 cell and move the Fill Handle over the cells where you want to implement this formula.
Contrary to the DATE function, the YEARFRAC function considers the start date while providing the input.
Hence, it is a more precise measure of the number of months between two dates.
Note that the YEARFRAC function uses the start date and the end date to calculate the fraction of the year between these two dates.
In most cases, this is going to be a decimal value, and when you multiply this value by 12, in many cases, you would still end up with a decimal value (such as 3.5)
3.5 means that 3 whole months have elapsed between the start date and the end date, and 0.5 portions of the month have also elapsed.
If you don’t want output in decimals, you can use the integer function or INT before the YEARFRAC functions, as shown below.
=INT(YEARFRAC(start_date, end_date)*12)
This will give you values in whole numbers.
Calculate the Number of Months Using the YEAR and MONTH Formula
The third method of calculating the number of months between two dates is by using the YEAR and MONTH formula.
You can use the following syntax to calculate the number of months:
=YEAR(end_date)-YEAR(start_date)*12+MONTH(end_date)-MONTH(start_date)
Below I have a data set where I have the start date and the end date, and I want to calculate the total number of months between these two dates
The YEAR and MONTH formula for this case will become:
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)
Press Enter to get the number of months value for the selected dates.
Select that cell and drag the Fill Handle to the remaining cells to apply the formula.
You will notice that some of the values generated by the YEAR and MONTH formulas are different from the DATEDIF function.
That’s because the DATEDIF function returns the total number of months that have passed between two dates.
On the other hand, the YEAR and MONTH formula returns the number of months that are covered in between two dates.
This means, even if a project lasted for a couple of days, the YEAR and MONTH formula will calculate it as one month.
Similar to the DATEDIF function, the YEAR and MONTH formula doesn’t count the start date while generating the values.
You can modify the formula and add “+1” to make it more accurate.
Here is how the modified YEAR and MONTH formula for counting the start date.
=(YEAR(end_date)-YEAR(start_date)*12+MONTH(end_date)-MONTH(start_date)+1)
Conclusion
Excel provides you with a variety of methods for calculating the number of months between two dates.
You can use either DATEDIF, YEARFRAC, or YEAR and MONTH functions to calculate the number of months between two dates.
Each function provides you with different values.
Ultimately, it depends on your data structure and which formula fulfills your requirements in a better way.
Other Excel articles you may also like: