Go Back

Get months between dates

To measure a timeline for an activity we calculate the difference between two dates in terms of days, weeks, months and years. But how can we calculate the difference between two dates in months in Excel? There are different formulas to get months between two dates in Excel.

Figure 1. Calculating Months Between Two Dates

Using the Excel DATEDIF Formula

The Excel DATEDIF function calculates the difference between two dates based on the interval specified like days, months, years. Using this function we can return the number of complete months between two dates as per the following syntax;

=DATEDIF( start_date, end_date, “M”)

The DATEDIF function rounds down the values by default and returns the complete number of months between dates. In our example, we can get months between two dates listed in column B and C as start date and end date values. We need to use the following formula in cell E2 and drag the fill handle down;

=DATEDIF(B2,C2,"M")

Figure 2. Using the DATEDIF Function to Calculate Months Between Two Dates

Using the YEAR and MONTH Formula

We can calculate months between two dates in Excel using the YEAR and MONTH functions in a formula as per the following syntax;

=(YEAR(end_date)-YEAR(start_date))*12+MONTH(end_date)-MONTH(start_date)  

This formula compares two given dates and calculates months between two dates either as a positive or negative number. We will apply this formula in cell F2 and copy the formula down, such as;

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)

Figure 3. Using the YEAR and MONTH Formula to Calculate Months Between Dates

Notice that the result of this formula is not always similar to the DATEDIF function. Because, the DATEDIF function returns the number of complete months elapsed between dates, and this formula works on the months’ numbers that belong to different dates. Like in row 6, the DATEDIF function returns 3 months and the YEAR/MONTH formula returns 4 because the dates belong to two different months.

Figure 4. The Difference Between DATEDIF and YEAR/MONTH Formulas

Using the YEARFRAC Formula

The Excel YEARFRAC function returns the year fraction representing the number of whole days between two dates. Using this function we can return the number of fractional months or absolute months between two dates as per the following syntax;

=YEARFRAC( start_date, end_date) * 12

In this example, we will use the following formula in cell G2 to get fractional months between two dates and copy it down to below cells;

=YEARFRAC(B2,C2)*12

Figure 5. Using the YEARFRAC Formula to Calculate Months Between Dates

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi, I want a formula to calculate the number months between dates and display text based on criteria for example. If 3 months critical if 6 months standard if less than 1 month critical
Solved by E. A. in 14 mins
group the dates into months
Solved by G. W. in 17 mins
I am trying to use datedif to calculate the number of months between two dates, and I keep on getting the result on a weird format instead of just a number
Solved by A. A. in 19 mins

Leave a Comment

avatar