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 an undocumented function inherited from Lotus 1-2-3. It works reliably in Google Sheets but does not appear in the autocomplete suggestions when you type in a cell. You need to type the full function name yourself.

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

Since DATEDIF does not appear in autocomplete, it is easy to mistype. If you get a #NAME? error, check your spelling. The function name is DATEDIF, not DATEDIFF (one F, not two).

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.