How to Use DATEDIF in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 7 min read · includes 2 free exercises

In short: DATEDIF returns the whole years, months or days between two dates. The formula is =DATEDIF(start_date, end_date, unit). Use "Y" for years, "M" for months and "D" for days.

What DATEDIF does

Subtracting dates gives days. DATEDIF gives whole units: how many full years have passed, or how many full months. That makes it the standard tool for ages, tenure and contract lengths.

DATEDIF syntax

=DATEDIF(start_date, end_date, unit)

The dates can be cell references, DATE calls or numbers. The unit goes in quotation marks. Per Google's help page:

UnitReturns
"Y"Whole years between the two dates
"M"Whole months between the two dates
"D"Total days between the two dates
"MD"Days remaining after subtracting whole months
"YM"Whole months remaining after subtracting whole years
"YD"Days between the dates, assuming they are less than one year apart

Months and years count only when they reach the same day of the month. Google's example: 9/30/15 to 2/28/16 returns 4 months, not 5. The page does not say whether the start date must come before the end date, so keep them in order.

A step-by-step example: age

Here is a birth date and a reference date in cells A1:B2. (In your own sheet, use TODAY() as the reference.)

AB
1Birth dateToday
25/20/199010/29/2026

Dates in the tables are shown in month/day/year order. The results do not depend on your display format.

=DATEDIF(A2, B2, "Y")

This returns 36. Here is why: from May 20, 1990 to May 20, 2026 is 36 whole years. The next birthday, in May 2027, has not come yet, so the count stays at 36.

Age in years and months

Combine two units for a precise age. After subtracting whole years, "YM" returns the whole months left:

=DATEDIF(A2, B2, "YM")

This returns 5: May 20 to October 20 is five whole months. Together, the age is 36 years and 5 months. To build the sentence in one cell, join the pieces with &.

Common mistakes

Try it yourself

Use the dates above (A2 and B2). Type your formula into the box, starting with =, then press Check.

Exercise 1: Age in years

Write a formula that returns the number of whole years between the birth date in A2 and the date in B2.

Hint: the unit is "Y".

Exercise 2: Leftover months

Write a formula that returns the whole months left over after whole years between A2 and B2.

Hint: the unit is "YM".

What to learn next

Syntax and rules in this guide were checked against Google's DATEDIF help page. See all guides.