DATEDIF in Google Sheets: Syntax, Examples & Free Practice

DATEDIF counts the whole years, months or days between two dates. It's the go-to formula for ages, years of service and contract lengths, because it handles the different lengths of months and leap years correctly.

Syntax

=DATEDIF(start_date, end_date, unit)

start_date: The earlier date.
end_date: The later date. Use TODAY() for "up to now".
unit: What to count, in quotes: "Y" years, "M" months, "D" days, "YM" months left after whole years, "MD" days left after whole months, "YD" days left after whole years.

Practice DATEDIF

DATEDIF examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

	A	B	C	D
1	Name	Birthdate	Hire date	Review
2	Maya	4/12/1988	9/2/2019	3/31/2026
3	Leo	11/30/1995	1/31/2022	6/15/2026
4	Ava	2/28/2001	7/14/2025	1/31/2026

Maya

4/12/1988

9/2/2019

3/31/2026

Leo

11/30/1995

1/31/2022

6/15/2026

Ava

2/28/2001

7/14/2025

1/31/2026

Staff records

Formula	Result	What it shows
`=DATEDIF(B2, D2, "Y")`	37	Maya's age on the date of her review.
`=DATEDIF(C2, D2, "M")`	78	Months between hiring and review.
`=DATEDIF(C2, D2, "Y")&" years, "&DATEDIF(C2, D2, "YM")&" months"`	6 years, 6 months	Years and months together.
`=DATEDIF(C3, D3, "D")`	1596	Days, the same as D3-C3.
`=DATEDIF(B2, TODAY(), "Y")`	Depends on today	Age today.
`=DATEDIF(D2, C2, "Y")`	#NUM!	The start date is after the end date.

Calculating an age in Google Sheets

The reliable formula is =DATEDIF(B2, TODAY(), "Y"), with the birthdate in B2. It counts complete years, so someone born on December 31 is still the younger age on December 30. Dividing the days by 365 looks simpler, but it's off by a day around birthdays because of leap years.

Years and months, like "3 years, 7 months"

Combine two DATEDIF calls: =DATEDIF(B2, TODAY(), "Y")&" years, "&DATEDIF(B2, TODAY(), "YM")&" months". "YM" gives the months left over after the whole years.

Tips and common mistakes

In Excel, DATEDIF is an undocumented leftover. In Google Sheets it is a fully supported, documented function.
The start date must be first. If it's later than the end date, DATEDIF returns #NUM!.
The "MD" unit can give surprising results when the start date is near the end of a month, so avoid it for anything that matters.
DATEDIF returns a number, not a date, so format the result cell as a number.

Related functions

Learning step by step? DATEDIF is covered in Lesson 40: TODAY, NOW & DATEDIF of the free course.

DATEDIF function in Google Sheets