How to calculate the number of months between two dates in Excel
Formula (Excel and Google Sheets)
=DATEDIF(B2,C2,"m")Use =DATEDIF(start, end, "m"). It counts complete months, so a month only counts once the same day of the month comes around again.
- Result on the example
- one result per row in
D2, filled down toD6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.
How it works
DATEDIF with "m" counts whole months between the two dates. Harbor Bakery signed up on the 15th and cancelled on the 14th, 22 calendar months later, so the last month isn't complete and the answer is 21. Ortiz Law ran from January 31 to February 28, which isn't a full month, so it shows 0.
=DATEDIF(B2,C2,"m")Result21
B23/15/2024The value in B2.
C21/14/2026The value in C2.
DATEDIF(B2,C2,"m")21DATEDIF: Difference in "Y", "M", "D", "YM", "MD" or "YD".- start_date
B23/15/2024 - end_date
C21/14/2026 - unit
"m"
- start_date
In Google Sheets
The same formula works in Google Sheets: =DATEDIF(B2,C2,"m"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) | 22, 12, 1…Different result | Counts calendar months crossed and ignores the day. Use it when any part of a month counts, as in monthly billing. Ignores the day of the month: January 31 to February 28 is 1. |
| =ROUND((C2-B2)/(365.25/12),1) | 22, 12, 1…Different result | Approximate months with a decimal, for averages and charts. Decimal months. |
Common mistakes
- DATEDIF returns #NUM! when the start date is after the end date. Swap them, or use MIN and MAX to put them in order.
- Dividing the days by 30 drifts: 365 days come out as 12.2 months. Use DATEDIF for whole months.
- DATEDIF isn't in Excel's autocomplete, so type it in full. It works in every version and in Google Sheets.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate the number of months between two dates
D2, calculate how many complete months each customer stayed, and fill down to D6.Questions
How do I get months and leftover days?
=DATEDIF(B2,C2,"m")&" months, "&DATEDIF(B2,C2,"md")&" days".
How do I count the months until today?
Use a cell with =TODAY() as the end date, like =DATEDIF(B2,F1,"m").
How do I get whole years instead?
Use "y": =DATEDIF(B2,C2,"y").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.