DATEDIF function

DATEDIF syntax

Argument	Required	What it means
start_date	Yes	The earlier date. If it's after end_date, DATEDIF returns #NUM!.
end_date	Yes	The later date: often TODAY(), or a fixed date in a cell.
unit	Yes	What to count, in quotes: "y" complete years, "m" complete months, "d" days; "ym" the months left after the whole years, "yd" the days left after the whole years, "md" the days left after the whole months (unreliable, see below). Capitals work too.

Examples

Age in whole years

The birthday in July hasn't come yet on 10 March 2026, so the age is still the lower number.

C2fx=DATEDIF(A2,B2,"y")

Row	A	B	C
1	Born	On	Age
2	22 Jul 1991	10 Mar 2026	34

=DATEDIF(A2,B2,"y") → 34Copy

Complete months of service

"m" counts only whole months: the 16th of the month hasn't come round again by 10 March, so the month that started on 16 February doesn't count yet.

C2fx=DATEDIF(A2,B2,"m")

Row	A	B	C
1	Started	On	Months
2	16 Sept 2024	10 Mar 2026	17

=DATEDIF(A2,B2,"m") → 17Copy

Years and months together

"y" gives the whole years and "ym" the months left over after them; joined with text, they read as a length of service.

C2fx=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"

Row	A	B	C
1	Started	On	Service
2	2 May 2019	10 Mar 2026	6 years, 10 months

=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months" → 6 years, 10 monthsCopy

Why "md" can't be trusted

From 31 January to 1 March is one whole month and one day, but "md" returns a negative number. It works as if 31 January plus a month were 31 February, which runs on to 3 March, two days after the end date.

C2fx=DATEDIF(A2,B2,"md")

Row	A	B	C
1	Start	End	md
2	31 Jan 2026	1 Mar 2026	-2

=DATEDIF(A2,B2,"md") → -2Copy

The days after the last whole month, done right

DATEDIF with "m" counts the whole months, EDATE moves the start date on by that many, and what's left until the end date is the extra days.

C2fx=B2-EDATE(A2,DATEDIF(A2,B2,"m"))

Row	A	B	C
1	Start	End	Days
2	31 Jan 2026	1 Mar 2026	1

=B2-EDATE(A2,DATEDIF(A2,B2,"m")) → 1Copy

Practise it

New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.

Your task: In C2, work out how many complete months the employee has worked, from the start date in B2 to the date in E2.

Graded live

C2fx

Row	A	B	C	D	E
1	Employee	Started	Months		As of
2	Lena	14 Aug 2023			10 Mar 2026

Write the formula in the bar above, then press Check or Enter.

Hint

DATEDIF takes the start date (B2), the end date (E2) and the unit "m".

Solution

=DATEDIF(B2,E2,"m") Any formula that returns the same result passes.

Check

Common mistakes

1. The dates the wrong way round. The start date must be the earlier one. The other way round, DATEDIF returns #NUM!.
2. Leaving out the quotes. The unit is text: "y", not y. Without the quotes Excel looks for a name called y and shows #NAME?.
3. Trusting "md". Around the end of a month "md" can return a negative number or zero, as Microsoft warns. Subtract EDATE of the start date instead, as in the last example.
4. Looking for it in the function list. Excel doesn't offer DATEDIF as you type or list it in Insert Function. Type it in full, with all three arguments, and it works.

The units, side by side

From 15 January 2023 to 10 March 2026, as Excel counts them:

Unit	What it counts	Result
"y"	Complete years	3
"m"	Complete months	37
"d"	Days	1150
"ym"	Months left after the whole years	1
"yd"	Days left after the whole years	54
"md"	Days left after the whole months	23

Here every unit gives the count you’d expect. "yd" can be a day out when a 29 February falls between the two dates’ month and day in one year but not the other, because Excel counts "yd" as if both dates were in the start date’s year. "md" goes wrong more often: whenever the end date’s day is lower than the start date’s and the month before the end date is too short to hold the start date’s day, as with a start on 29, 30 or 31 January and an end date early in March.