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;
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;
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;
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;
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.