Complete years
| A | B | |
|---|---|---|
| 1 | Start | End |
| 2 | 1/15/2020 | 3/20/2024 |
Formula: D2: =DATEDIF(A2,B2,"y")
Result: 4
From Jan 15, 2020 to Mar 20, 2024 is four complete years.
Date & time
DATEDIF
Difference between dates in "y", "m" or "d" (undocumented, but works).
Syntax
DATEDIF(start_date, end_date, unit)Example: =DATEDIF(B2,TODAY(),"y")
Available in: All versions
DATEDIF returns the number of complete years, months or days between two dates: =DATEDIF(start, end, unit), with units "y", "m", "d", "ym", "md" and "yd". It is a long-standing function that does not appear in the formula autocomplete, but it works.
When to use it
- You calculate an exact age or years of service.
- You need whole months between two dates, such as a subscription length.
- You want a "4 years, 2 months" style label.
- You need the leftover days or months after counting full years.
Arguments explained
- start_date. The earlier date. If it is later than end_date, the result is #NUM!.
- end_date. The later date, for instance TODAY() when you need an age or tenure.
- unit. In quotes: "Y" complete years, "M" complete months, "D" days, "YM" months ignoring years, "YD" days ignoring years, "MD" days ignoring months and years. "MD" can give wrong results and is best avoided.
- Availability. DATEDIF works in all versions but is missing from the function wizard, so you must type it yourself.
Worked examples
Months left over
| A | B | |
|---|---|---|
| 1 | Start | End |
| 2 | 1/15/2020 | 3/20/2024 |
Formula: D2: =DATEDIF(A2,B2,"ym")
Result: 2
"ym" ignores years and counts the whole months after the last anniversary.
Readable duration
| A | B | |
|---|---|---|
| 1 | Start | End |
| 2 | 1/15/2020 | 3/20/2024 |
Formula: D2: =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"
Result: 4 years, 2 months
Joins two DATEDIFs into one label.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =DATEDIF(A2,B2,"m")
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Start | End | Holiday | Time | Result | |
| 2 | 3/15/2026 | 6/20/2026 | 5/1/2026 | 14:45 | 3 | |
| 3 | 1/31/2026 | 2/28/2026 | 5/9/2026 | 09:30 | ||
| 4 | 12/24/2025 | 1/9/2026 |
Result in F2: 3
Common errors and how to fix them
- #NUM!. The start date is later than the end date. Swap them.
- Not in autocomplete. DATEDIF is undocumented in the function list, but typing it works.
- "md" gives odd results. Microsoft documents known problems with the "md" unit. Avoid it, or compute days with subtraction.
- #VALUE!. A date is stored as text. Convert it with DATEVALUE.
Related functions
Frequently asked questions
- How do I calculate age in Excel?
- Use =DATEDIF(birth_date,TODAY(),"y").
- What are the units of DATEDIF?
- "y" years, "m" months, "d" days, "ym" months ignoring years, "yd" days ignoring years, "md" days ignoring months and years.
- Can I use simple subtraction instead?
- For days, yes: end minus start. For months and years DATEDIF counts complete periods.
- Why is DATEDIF missing from the function list?
- It comes from Lotus 1-2-3 compatibility and was never added to the autocomplete.