DATEDIF in Google Sheets
Use DATEDIF to measure the gap between two dates in whole years, months, or days — perfect for age, tenure, or how long something has been open.
=DATEDIF(A2, B2, "Y")
Returns the number of complete years between the start date A2 and the end date B2.
How it works
DATEDIF takes a start date, an end date (which must be later), and a unit in quotes: "Y" for whole years, "M" for whole months, "D" for days, and "YM"/"MD"/"YD" for the leftover part after the larger unit. It's a hidden function — Sheets won't autocomplete it — but it works when typed. For age today, use TODAY() as the end date.
Variations
Age today in years
=DATEDIF(A2, TODAY(), "Y")
Uses today's date as the end, so it updates every day.
Whole months between dates
=DATEDIF(A2, B2, "M")
Counts complete months, ignoring leftover days.
Years and months together
=DATEDIF(A2, B2, "Y")&" yr "&DATEDIF(A2, B2, "YM")&" mo"
"YM" is the months left over after full years — combine for a readable duration.
Days ignoring years
=DATEDIF(A2, B2, "YD")
Days between the dates as if they were in the same year.
Examples
| Scenario | Formula |
|---|---|
| Employee tenure in years | =DATEDIF(A2, TODAY(), "Y") |
| Months a ticket stayed open | =DATEDIF(A2, B2, "M") |
| Days until a deadline | =DATEDIF(TODAY(), B2, "D") |
FAQ
Why does DATEDIF return #NUM!?
The start date is later than the end date. DATEDIF requires the first date to be earlier — swap the arguments.
Why doesn't DATEDIF autocomplete?
It's an undocumented legacy function, so Sheets won't suggest it, but typing it out in full works normally.
What does "YM" mean?
It returns the number of months left over after counting the whole years — useful for showing an age like "3 yr 5 mo".