Date & time · date difference · Nov 16, 2026

The DATEDIF function,
measured & explained.

DATEDIF measures the gap between two dates in six units — Y/M/D for totals and YM/MD/YD for the remainder ignoring larger units. It is the hidden age/tenure function. This page measures all six units and the reversed-date trap live.

D1
fx
=DATEDIF(start, end, "Y")
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How DATEDIF works

Returns the complete-unit difference. start must be ≤ end or it raises #NUM!; units are case-sensitive in this engine.

=DATEDIF(start_date, end_date, unit)
start_dateThe earlier date (must be ≤ end).
end_dateThe later date.
unitY/M/D totals, or YM/MD/YD remainders (uppercase).
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Six units

What DATEDIF actually returns

DATEDIF measures the gap between two dates in six units — Y/M/D for totals, YM/MD/YD for the remainder ignoring larger units. It is the hidden age/tenure function. Every row measured live. Click any row to load it.

Six-unit gallery (2020-03-15 → 2024-07-20)

ExpressionResultWhy
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"Y")4Complete years
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"M")52Complete months
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"D")1588Total days
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"YM")4Months ignoring years
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"MD")5Days ignoring months/years
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"YD")127Days ignoring years
=DATEDIF(DATE(2024,7,20),DATE(2020,3,15),"D")#NUM!Reversed dates raise #NUM!
=DATEDIF(DATE(2024,1,15),DATE(2024,12,31),"M")11Complete months within a year

Build an age string

ExpressionResultWhy
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"Y")4years
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"YM")4remaining months
=DATEDIF(DATE(2020,3,15),DATE(2024,7,20),"MD")5remaining days
⚠ start must be ≤ end — reversed dates raise #NUM!. Units are case-sensitive in this engine: use uppercase "Y"/"M"/"D"/"YM"/"MD"/"YD" (lowercase errors here). DATEDIF is undocumented in Excel's function list but works.
Y + YM + MD compose a full age: 4 years, 4 months, 5 days. YM is months after removing whole years; MD is days after removing whole months; YD is days after removing whole years.
// 03 — Try it live

Interactive DATEDIF playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see DATEDIF return complete years, months, days, and the YM/MD/YD remainders for an age string.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where DATEDIF works

DATEDIF works in Excel and Google Sheets as a legacy compatibility function. Unit case-sensitivity differs by engine — this page uses uppercase units, which are safest across platforms.

PlatformSupportNotesGotcha
✓ Supported full DATEDIF◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master date differences

DATEDIF is one piece. The full course walks the toolkit end to end with live, measured examples.

  • The six units and what each ignores
  • Building an age/tenure string (Y+YM+MD)
  • The reversed-date #NUM! trap
  • Why DATEDIF is hidden but works
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked