If you want to calculate the number of months between two dates in Excel, subtracting one date from the other will only give you days.
That is because months do not all contain the same number of days.
Excel can return completed months, decimal months, or every calendar month touched. In this article, I’ll show you the formula for each result, including versions that update automatically with TODAY.
All formulas below assume the end date is on or after the start date. If it is not, swap the dates or validate them first.
Calculate Completed Months Between Two Dates Using DATEDIF
Use this method when you only need the number of complete months that have passed between the start and end dates.
Below is a project list with start dates in column B and end dates in column C. I want the completed months in column D.
Enter this formula in cell D2:
=DATEDIF(B2:B6,C2:C6,"m")
The DATEDIF formula measures the gap between each pair of dates. The “m” argument tells Excel to return only completed months.
Pro Tip: DATEDIF is a legacy compatibility function that Excel keeps for older Lotus 1-2-3 workbooks. Its syntax is DATEDIF(start_date,end_date,unit), and the “m” unit returns completed months.
For example, January 15 to March 14 returns 1 because two complete months have not passed. January 15 to March 15 returns 2.
In Excel versions that support dynamic arrays, this one formula spills all five results into column D.
Pro Tip: If your Excel version does not spill the results, use =DATEDIF(B2,C2,"m") in D2 and fill it down. If you get a #SPILL! error, clear the cells below the formula. Spilling formulas cannot be used inside an Excel Table’s calculated column.
Important: DATEDIF returns a #NUM! error when the start date comes after the end date. Dates stored as text can also cause a #VALUE! error. Correct the source dates instead of hiding the error.
Calculate Months From a Date to Today
When one side of the calculation is the current date, TODAY can keep the result up to date automatically.
Below is a project list with start dates in column B and planned end dates in column C. I want the completed months so far in column D and the complete months remaining in column E.
Enter this formula in D2 to calculate the completed months from each start date through today:
=DATEDIF(B2:B6,TODAY(),"m")
To calculate the complete months remaining from today to each planned end date, enter this formula in E2:
=DATEDIF(TODAY(),C2:C6,"m")
The TODAY() formula supplies the current date. Excel updates the result whenever the workbook recalculates.
The same DATEDIF and TODAY setup can also calculate age in Excel from a date of birth.
Pro Tip: TODAY keeps changing. Use a fixed as-of date in another cell when you need a historical report that should not change later.
Important: The first formula returns #NUM! for a future start date. The second returns #NUM! after a planned end date has passed. Check the date order before using either formula.
Calculate Months Between Two Dates Without DATEDIF
You can calculate completed months with YEAR, MONTH, and DAY when you do not want to use DATEDIF.
Below is a project list with start dates in column B and end dates in column C. I want the completed months in column D.
Enter this formula in D2:
=12*(YEAR(C2:C6)-YEAR(B2:B6))+MONTH(C2:C6)-MONTH(B2:B6)-(DAY(C2:C6)<DAY(B2:B6))
The YEAR portion converts the difference in years to months. The MONTH portion adds the month-number difference.
The final DAY test subtracts 1 when the end day is earlier than the start day. That removes a month that has not been completed.
This formula spills down automatically in Excel versions that support dynamic arrays. In older versions, change the ranges to B2 and C2, then fill the formula down.
Show the Difference as Months and Days
Sometimes a completed-month count is too broad, and you also need the remaining days after the last monthly anniversary.
Below is a project list with start dates in column B and end dates in column C. I want a result such as “2 Months 14 Days” in column D.
Enter this formula in D2 and fill it down:
=LET(start,B2,finish,C2,wholeMonths,12*(YEAR(finish)-YEAR(start))+MONTH(finish)-MONTH(start)-(DAY(finish)<DAY(start)),remainingDays,finish-EDATE(start,wholeMonths),wholeMonths&IF(wholeMonths=1," Month "," Months ")&remainingDays&IF(remainingDays=1," Day"," Days"))
The formula first finds the completed months using the same YEAR, MONTH, and DAY logic shown earlier. EDATE then moves the start date forward by that many months, and Excel subtracts that anniversary from the end date.
This avoids DATEDIF’s “md” argument. Microsoft warns that “md” can return a negative, zero, or inaccurate result for some date combinations.
LET is available in Microsoft 365, Excel 2024, and Excel 2021. This calculation stays per row because EDATE does not produce this result as a clean spilling range.
Calculate Decimal Months Using YEARFRAC
Use YEARFRAC when you need the difference expressed as an approximate decimal number of months.
Below is a project list with start dates in column B and end dates in column C. I want the decimal months rounded to two places in column D.
Enter this formula in D2 and fill it down:
=ROUND(YEARFRAC(B2,C2,1)*12,2)
YEARFRAC returns the fraction of a year between the two dates. Multiplying it by 12 converts that year fraction to months.
The third argument, 1, tells Excel to use the actual number of days in the relevant years. ROUND returns the result rounded to two decimal places.
Important: Decimal months are an approximation because calendar months have different lengths. The decimal part is a fraction of the year converted to months, not a direct count of leftover days.
If your calculation depends on the exact number of days in a month, calculate that separately instead of treating every month as 30 days.
If you are starting with a number of days rather than two dates, use the separate guide to Convert Days to Months in Excel.
Count Month Boundaries or Calendar Months Touched
The formulas above measure elapsed time. A project report may instead need the number of month changes or every calendar month in which the project was active.
Below is a project list with start dates in column B and end dates in column C. I want both counts for each project.
Enter this formula in D2 to count the month boundaries crossed:
=(YEAR(C2:C6)-YEAR(B2:B6))*12+MONTH(C2:C6)-MONTH(B2:B6)
January 31 to February 1 crosses one month boundary, so this formula returns 1 even though only one day has passed.
To count every calendar month touched, including the start month, enter this formula in E2:
=(YEAR(C2:C6)-YEAR(B2:B6))*12+MONTH(C2:C6)-MONTH(B2:B6)+1
For January 31 to February 1, the inclusive formula returns 2 because the project was active in both January and February.
This calendar-month method is useful for monthly activity reports. It does not measure complete elapsed months, so do not use it as a substitute for DATEDIF.
The Calculating Time In Excel guide covers differences that include hours, minutes, or seconds.
I covered completed months, decimal months, month boundaries, and calendar months touched. I also showed formulas that use TODAY when one of the dates needs to update automatically.
I hope you found this article helpful.
Other Excel Articles You May Also Like:
- How to Calculate the Number of Days Between Two Dates in Excel
- How to Remove Time from Date/Timestamp in Excel
- Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
- How to Quickly Insert Date and Timestamp in Excel
- Convert Date to Text in Excel
- How to SUM values between two dates in Excel
- How to Add Months to Date in Excel
- How to Calculate Years of Service in Excel (Easy Formulas)
- How to Make an Interactive Calendar in Excel? (FREE Template)
- Calculate Date Difference in Power Query
