DATEDIF Function

Counts complete years, months or days between two dates.

Category

Date & Time

Available in

All versions (not listed in Insert Function)

Returns

A whole number

Want to practice DATEDIF in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

DATEDIF is Excel's hidden function for ages and service length. It counts complete years, months or days between two dates.

It doesn't appear in Insert Function or AutoComplete — type it in full and it still works.

Syntax

=DATEDIF(start_date, end_date, unit) Explain

Arguments

ArgumentRequired?What it does
start_dateYesThe earlier date.
end_dateYesThe later date. If it's before start_date, the result is #NUM!.
unitYes"Y" years, "M" months, "D" days, "YM" months left over after years, "YD" days left over after years, "MD" days left over after months.

Examples

Example 1 — Years of service

Complete years between a hire date and the review date.

C2fx=DATEDIF(A2,B2,"Y")
ABC
1HiredReview dateYears
25/20/19903/15/202635
39/1/20193/15/20266
43/15/20203/15/20266
=DATEDIF(A2,B2,"Y") Explain

Example 2 — Years, months and days

Combine units to describe the exact gap between 5/20/1990 (A2) and 3/15/2026 (B2).

D2fx=DATEDIF(A2,B2,C2)
ABCD
1StartEndUnitResult
25/20/19903/15/2026Y35
35/20/19903/15/2026YM9
45/20/19903/15/2026MD23
55/20/19903/15/2026M429
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months" Explain

35 years, 9 months — or 429 complete months in total.

Common mistakes

  • #NUM! The start date is after the end date. Swap them.
  • "MD" gives odd answers. Microsoft warns that "MD" can return wrong or negative results near month ends. Prefer "Y", "M", "YM" and "D".

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.