DATEDIF Function in Google Sheets
Calculates the difference between two dates in years, months, or days. The cleanest way to compute age, tenure, or any "how long between" metric.
Syntax
DATEDIF(start_date, end_date, unit)Returns: The difference between dates in the specified unit.
Excel equivalent: DATEDIF (identical, undocumented in both)
Parameters
| Name | Required | Description |
|---|---|---|
| start_date | Required | The earlier date. |
| end_date | Required | The later date. Must be ≥ start_date. |
| unit | Required | "Y" = full years, "M" = full months, "D" = days, "MD" = day-of-month remainder, "YM" = month remainder ignoring year, "YD" = day remainder ignoring year. |
Examples
Age in years from birthdate
=DATEDIF(A2, TODAY(), "Y")Returns full years between birthdate in A2 and today. Doesn't round — 25 years and 11 months returns 25.
Tenure as 'X years, Y months'
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"Combines years with leftover months. "YM" returns months after subtracting full years.
Days between contract dates
=DATEDIF(A2, B2, "D")Pure day count between two dates.
When to use an alternative
- Subtraction (end - start) — You want raw day count — gives same result as DATEDIF with "D".
- NETWORKDAYS — You want only business days (excluding weekends/holidays).
- YEARFRAC — You want fractional years instead of whole years.
Common errors and how to fix them
#NUM!
Cause: start_date is greater than end_date.
Fix: Wrap with IF or swap argument order. Or use ABS(end - start) for absolute day count.
Unit not recognized
Cause: Typo in the unit string.
Fix: Use exact strings: "Y", "M", "D", "MD", "YM", "YD".
Related functions
Frequently Asked Questions
Why does DATEDIF return one less than expected?
DATEDIF returns FULL units only. 11 months and 28 days returns 0 years and 11 months. To get "closest" rather than "completed", round YEARFRAC: ROUND(YEARFRAC(start, end), 0).
Is DATEDIF deprecated?
It's not officially deprecated but it's also not in Google's documentation (a holdover from Excel compatibility). It still works reliably and is the cleanest option for years/months/days math. Use it confidently.