DATEDIF measures the elapsed time between two dates in whole years, whole months, total days, or remainder units. The unit code determines which result you receive.
Use =DATEDIF(start_date,end_date,"D") for total days. Use Y, M, YM, YD, or MD for other calendar-based intervals.
DATEDIF Function Syntax in Google Sheets
=DATEDIF(start_date, end_date, unit)- start_date is the earlier date.
- end_date is the later date.
- unit is a quoted code: Y, M, D, YM, YD, or MD.
| Unit | Returns |
|---|---|
| Y | Whole years |
| M | Whole months across the full period |
| D | Total days |
| YM | Whole months after subtracting whole years |
| YD | Days after subtracting whole years |
| MD | Days after subtracting whole months |
When to Use DATEDIF
- Calculate age in whole years.
- Measure subscription or employee tenure in months.
- Count elapsed calendar days.
- Build a readable years, months, and days label.
- Find the interval since an anniversary.
Calculate the Difference in Whole Years
Cells A2 and B2 contain June 15, 2016, and September 20, 2026:
=DATEDIF(A2,B2,"Y")The tested result is 10. DATEDIF counts a year only after the ending date reaches that anniversary.
For current age, replace B2 with TODAY:
=DATEDIF(A2,TODAY(),"Y")Calculate the Difference in Whole Months
=DATEDIF(A2,B2,"M")The tested result is 123. M counts whole months across the complete period, including months inside the ten whole years.
Calculate the Difference in Days
=DATEDIF(A2,B2,"D")The tested result is 3749. For total days only, =B2-A2 returns the same elapsed gap.
Format the output as Number. A Date format can make 3749 appear as an unrelated date.
Return Months After Whole Years
=DATEDIF(A2,B2,"YM")YM ignores the ten whole years and returns the remaining 3 whole months.
Return Days After Whole Months
=DATEDIF(A2,B2,"MD")MD removes whole months and returns the remaining 5 days.
Return Days After Whole Years
=DATEDIF(A2,B2,"YD")YD measures from the most recent anniversary. The tested result is 97 days.
Build a Years, Months, and Days Label
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"The tested result is 10 years, 3 months, 5 days. Y supplies years, YM supplies leftover months, and MD supplies leftover days.
Handle Date Order and Unit Errors
Equal start and end dates return 0 for the D unit. An end date before the start returns #NUM!.
Unit codes require quotation marks. Enter "Y", not an unquoted Y.
Use DATEVALUE first when your date inputs are text. Use EDATE when you need to move a date by months.
Google’s DATEDIF reference defines all six units and its month-boundary rule.
Other Google Sheets articles you may also like