How to Use DATEDIF in Google Sheets
=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:
| Unit | Returns |
|---|---|
"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.)
| A | B | |
|---|---|---|
| 1 | Birth date | Today |
| 2 | 5/20/1990 | 10/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
- The result shows as a date. Google's page says a result like 1/4/1900 means the cell has a Date format. Change it to Number.
- Forgetting the quotation marks around the unit.
- Reversed dates. Put the earlier date first.
- Expecting rounded results. DATEDIF counts only complete units.
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.
Exercise 2: Leftover months
Write a formula that returns the whole months left over after whole years between A2 and B2.
What to learn next
- Days between two dates: three ways to count the gap.
- TODAY: return the current date.
- DATE: build a date from a year, month and day.
Syntax and rules in this guide were checked against Google's DATEDIF help page. See all guides.