What DATEDIF Does
The DATEDIF function calculates the difference between two dates in a unit you choose — years, months, days, or a combination. Despite being one of the most useful date functions in Google Sheets, it is undocumented in the official function list (it was inherited from Lotus 1-2-3). It works reliably and is the simplest way to calculate someone's age, tenure, or the time between any two events.
Syntax
=DATEDIF(start_date, end_date, unit)
| Parameter | Description |
|---|---|
| start_date | The earlier date. |
| end_date | The later date. Must be on or after start_date. |
| unit | A text code that determines what to measure. |
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. |
"YM" | Months remaining after subtracting complete years. |
"YD" | Days remaining after subtracting complete years. |
Basic Examples
Calculate age in years
| A | B | |
|---|---|---|
| 1 | Birthdate | Age |
| 2 | 7/14/1990 | =DATEDIF(A2, TODAY(), "Y") |
Result in B2: 35
This returns the number of complete years between the birthdate and today.
Find the total months between two dates
| A | B | |
|---|---|---|
| 1 | Start | End |
| 2 | 1/1/2024 | 4/5/2026 |
=DATEDIF(A2, B2, "M")
Result: 27
Measure total days for a project
| A | B | |
|---|---|---|
| 1 | Kickoff | Launch |
| 2 | 9/15/2025 | 4/5/2026 |
=DATEDIF(A2, B2, "D")
Result: 202
Advanced Examples
Display age as "X years, Y months, Z days"
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"
For a birthdate of 7/14/1990, this might return 35 years, 8 months, 22 days. The "YM" unit gives months after removing full years, and "MD" gives remaining days after removing full months.
Calculate employee tenure and group by seniority
Suppose column A has hire dates and you want to classify employees:
=IF(DATEDIF(A2, TODAY(), "Y") >= 10, "Senior",
IF(DATEDIF(A2, TODAY(), "Y") >= 3, "Mid-level", "Junior"))
This labels anyone with 10+ years as Senior, 3-9 years as Mid-level, and under 3 years as Junior. Because it uses TODAY, the labels update automatically as time passes.
Common Mistakes
- Putting the end date before the start date. DATEDIF throws a
#NUM!error ifstart_dateis later thanend_date. If you are not sure which date comes first, guard against it:=DATEDIF(MIN(A2,B2), MAX(A2,B2), "D"). - Relying on the "MD" unit for critical calculations. The
"MD"unit has known quirks in edge cases (for example, when spanning certain month boundaries). For high-stakes work, consider using DAY functions instead:=DAY(end_date) - DAY(start_date)with appropriate adjustments. - Expecting DATEDIF to appear in autocomplete. Because it is officially undocumented, Google Sheets does not suggest it as you type. You have to enter the full formula manually. It works — it just will not pop up in the suggestion list.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.