SheetsdrillDATEDIF
DATEDIF(start_date, end_date, unit) returns the whole number of years ("Y"), months ("M") or days ("D") between two dates, plus "YM", "MD" and "YD" for the remainders. It is the standard way to calculate age, tenure and contract length.
Syntax
=DATEDIF(start_date, end_date, unit)| Part | What it is |
|---|---|
start_date | The earlier date, as a cell, DATE() or a date string. |
end_date | The later date; must not be before start_date. |
unit | "Y" whole years, "M" whole months, "D" days, "YM" months left after whole years, "MD" days left after whole months, "YD" days left after whole years. |
Examples
=DATEDIF(DATE(2020,3,15), DATE(2026,11,3), "Y")Result: 6
=DATEDIF(DATE(2020,3,15), DATE(2026,11,3), "M")Result: 79
=DATEDIF(DATE(2020,3,15), DATE(2026,11,3), "D")Result: 2424
Same as the plain subtraction of the two dates.
Common mistakes
- #NUM! because the start date is after the end date; swap the arguments or wrap in ABS of a subtraction for days.
- A date typed as text ("3/15/2020") can be read in the wrong locale; use DATE(2020,3,15) or a real date cell.
- "MD" gives odd results around month ends (for example January 31 to March 1); prefer "D" or a month-based calculation for billing.
Practice DATEDIF
These drills check your answer on live data, free, no account needed.
- Full years of serviceWork out each employee's completed years of service on the report date in F1. Fill C2 and C3 using DATEDIF.
Questions
How do I calculate age in Google Sheets?
=DATEDIF(B2, TODAY(), "Y") where B2 is the birth date.
How do I get the months between two dates?
=DATEDIF(A2, B2, "M") for whole months. For a count of month boundaries crossed, use (YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2).
Related
- TODAY and NOWHow TODAY and NOW in Google Sheets return the current date and time, how to calculate days until a deadline, age and overdue flags, and why the value changes.
- NETWORKDAYSHow NETWORKDAYS and NETWORKDAYS.INTL in Google Sheets count working days between two dates, exclude holidays, use custom weekends, and add days with WORKDAY.
- EOMONTH and EDATEHow EOMONTH and EDATE in Google Sheets return the last day of a month or the same day N months later, with examples for due dates and first of month.
- DATE, YEAR, MONTH and DAYHow DATE, YEAR, MONTH and DAY work in Google Sheets to build a date from parts, pull the year or month out of a date, group by month and fix text dates.