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
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| start_date | Yes | The earlier date. |
| end_date | Yes | The later date. If it's before start_date, the result is #NUM!. |
| unit | Yes | "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.
| A | B | C | |
|---|---|---|---|
| 1 | Hired | Review date | Years |
| 2 | 5/20/1990 | 3/15/2026 | 35 |
| 3 | 9/1/2019 | 3/15/2026 | 6 |
| 4 | 3/15/2020 | 3/15/2026 | 6 |
Example 2 — Years, months and days
Combine units to describe the exact gap between 5/20/1990 (A2) and 3/15/2026 (B2).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Unit | Result |
| 2 | 5/20/1990 | 3/15/2026 | Y | 35 |
| 3 | 5/20/1990 | 3/15/2026 | YM | 9 |
| 4 | 5/20/1990 | 3/15/2026 | MD | 23 |
| 5 | 5/20/1990 | 3/15/2026 | M | 429 |
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
- For a decimal number of years (2.5 rather than 2), use YEARFRAC.
- Related functions: YEARFRAC, DAYS, TODAY.

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.