DATEDIF Function in Google Sheets

DATEDIF in Google Sheets calculates the difference between two dates in the unit you specify: years, months, or days. It is the standard way to compute age, tenure, duration, and other date intervals.

DATEDIF is inherited from Lotus 1-2-3 and is often called a hidden function. In Google Sheets it is not hidden: typing =DATEDIF brings up the autocomplete suggestion, and it is listed under Insert > Function > Date.

Parameters

Parameter	Required	Description
start_date	Yes	The starting date. Must be earlier than or equal to end_date. Can be a cell reference, date value, or DATE() function.
end_date	Yes	The ending date. Must be greater than or equal to start_date.
unit	Yes	A text string specifying the output unit. Must be one of the six codes below.

Unit codes

Unit	Returns
"Y"	Complete years between the dates
"M"	Complete months between the dates
"D"	Total days between the dates
"MD"	Days remaining after subtracting complete months (ignores months and years)
"YM"	Months remaining after subtracting complete years (ignores years)
"YD"	Days remaining after subtracting complete years (ignores years)

Examples

Calculate age from a birthdate

Find someone’s age in complete years using their birthdate in A2:

=DATEDIF(A2, TODAY(), "Y")

If A2 is 1990-06-15 and today is 2026-02-19, this returns 35. The "Y" unit counts only complete years, so the result does not round up.

Months between two dates

Calculate the total number of complete months between a start date and an end date:

=DATEDIF(B2, C2, "M")

If B2 is 2025-03-01 and C2 is 2026-02-19, this returns 11. The partial month at the end is not counted.

Display age as years and months

Combine multiple DATEDIF calls to show a formatted duration:

=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"

If A2 is 1990-06-15, this returns something like “35 years, 8 months”. The "YM" unit gives the leftover months after complete years are subtracted.

Common Errors

#NUM! --- The start date is after the end date. DATEDIF requires that start_date comes before or equals end_date. Swap the arguments, or use =IF(A2 > B2, "Invalid dates", DATEDIF(A2, B2, "Y")) to catch the issue.

#VALUE! --- The unit string is not one of the six valid codes, or a date argument is not a valid date. Double-check that the unit is in quotes and spelled correctly: "Y", "M", "D", "MD", "YM", or "YD".

#NUM! with “MD” unit --- The "MD" unit can occasionally produce unexpected errors with certain date combinations. If you hit this, use an alternative approach: =DAY(end_date) - DAY(start_date) adjusted for negative values.

Tips

If you get a #NAME? error, check your spelling. The function name is DATEDIF, not DATEDIFF (one F, not two). Letting the autocomplete finish the name for you avoids this entirely.

For a quick “years, months, days” breakdown, you can chain three DATEDIF calls: one with "Y", one with "YM", and one with "MD". This gives the same kind of result you see on age calculator websites.