=DATEDIF(A2,B2,"M") counts the complete months between the start date in A2 and the end date in B2. The third argument, the unit, decides what is counted: "Y" years, "M" months, "D" days, plus three units that ignore part of the dates.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Unit | Result | |
| 2 | 2024-04-15 | 2026-09-10 | Y | 2 | |
| 3 | M | 28 | |||
| 4 | D | 878 | |||
| 5 | YM | 4 | |||
| 6 | MD | 26 | |||
| 7 | YD | 148 |
From 2024-04-15 to 2026-09-10 there are 2 complete years, 28 complete months and 878 days. Change the end date in B2 and watch each unit move. The unit can be typed in the formula ("M") or, as here, read from a cell.
DATEDIF syntax and units
=DATEDIF(start_date, end_date, unit)
| Unit | Counts | 2024-04-15 to 2026-09-10 |
|---|---|---|
"Y" | complete years | 2 |
"M" | complete months | 28 |
"D" | days | 878 |
"YM" | months after the last complete year | 4 |
"MD" | days after the last complete month | 26 |
"YD" | days after the last complete year | 148 |
"Complete" means DATEDIF never rounds up. From January 31 to February 28 is 0 months, because February 28 comes before the one-month mark. The unit is not case-sensitive ("m" works too), but it must be in quotes or in a cell.
Why DATEDIF is not in the function list
DATEDIF came to Excel from Lotus 1-2-3, and Microsoft keeps it only for compatibility. It is not in the Insert Function dialog, formula autocomplete does not suggest it, and no tooltip shows its arguments. It still calculates in every version of Excel on Windows and Mac, and in Google Sheets. Type the whole name and the opening bracket, =DATEDIF(, and add the arguments yourself.
Months between two dates
Complete months are what most contracts, subscriptions and tenure reports need. For a decimal figure, YEARFRAC(A2,B2)*12 returns the months with the fraction kept (its default basis counts every month as 30 days).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Full months | Decimal months |
| 2 | 2025-03-10 | 2026-01-05 | 9 | 9.8 |
| 3 | 2024-04-15 | 2026-09-10 | 28 | 28.8 |
| 4 | 2026-01-31 | 2026-02-28 | 0 | 0.9 |
| 5 | 2025-11-01 | 2026-05-01 | 6 | 6.0 |
The first subscription ran 9 complete months: January 5 is before the 10th, so the tenth month is not counted. The third row shows the January 31 case, 0 months.
Years, months and days together
"Y", "YM" and "MD" are made to be used together. Each one picks up where the previous one stopped, so the three answer "how long, exactly": 2 years, 4 months and 26 days for the dates above. Calculate age uses the same three units on birth dates.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hired | Years | Months | Days | Service | As of |
| 2 | 2019-06-03 | 7 | 3 | 27 | 7y 3m 27d | 2026-09-30 |
| 3 | 2023-11-20 | 2 | 10 | 10 | 2y 10m 10d |
The first employee has served 7 years, 3 months and 27 days on 2026-09-30.
Why DATEDIF returns #NUM!
DATEDIF refuses to count backwards. When the start date is later than the end date, the result is #NUM!. If the order can vary, give DATEDIF the earlier date with MIN and the later one with MAX.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | DATEDIF | With MIN and MAX |
| 2 | 2026-03-10 | 2026-01-05 | #NUM! | 64 |
#NUM! A number is out of range for this function.C2 shows #NUM!; D2 returns 64. For days alone, =ABS(B2-A2) gives the same 64 without DATEDIF. DATEDIF also returns #NUM! for an unknown unit such as "W", and #VALUE! when a date is text that Excel cannot read as a date.
The MD unit can be wrong
Microsoft's own documentation advises against "MD": when the start day does not exist in the month before the end date, it can return a negative number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | MD | D |
| 2 | 2026-01-31 | 2026-03-01 | -2 | 29 |
From January 31 to March 1 the "MD" result is -2: DATEDIF counts from "February 31", which rolls over to March 3, two days after the end date. The day count, 29, is right. When start dates can fall on the 29th to the 31st, show the length in days, or check the MD figure by hand.
Practice: count the months
| A | B | C | |
|---|---|---|---|
| 1 | Start | End | Months |
| 2 | 2025-03-10 | 2026-01-05 |
Your turn: In C2, count the complete months between the start date in A2 and the end date in B2.
| A | B | C | |
|---|---|---|---|
| 1 | Date 1 | Date 2 | Days |
| 2 | 2026-04-18 | 2026-02-01 |
Your turn: In C2, count the days between the two dates, whichever one comes first.
Frequently Asked Questions
What does DATEDIF do in Excel?
It counts the complete years, months or days between two dates: =DATEDIF(A2,B2,"Y") for years, "M" for months, "D" for days. The start date must come first.
Why can't I find DATEDIF in Excel?
DATEDIF is kept for compatibility with Lotus 1-2-3, so Excel leaves it out of the Insert Function dialog and formula autocomplete. It still works in every version: type =DATEDIF( in full and add the arguments yourself.
Why does DATEDIF return #NUM!?
The start date is later than the end date. Swap the arguments, or use =DATEDIF(MIN(A2,B2),MAX(A2,B2),"D") when you don't know which date comes first.
What is the difference between YM, MD and YD in DATEDIF?
They ignore part of the dates: "YM" gives the months left after the full years, "MD" the days left after the full months, and "YD" the days left after the full years. Together "Y", "YM" and "MD" give an age in years, months and days.
Does DATEDIF work in Google Sheets?
Yes, with the same arguments and units. Google Sheets also lists it in its function help, unlike Excel.
