Date and time
DATEDIF function playground
Counts the complete years, months or days between two dates.
The playground runs in your browser. Nothing you type is sent anywhere.
How DATEDIF works
DATEDIF counts only complete units. From 11 March 2019 to 1 October 2026 is 7 complete years, because the 8th anniversary hasn't arrived.
Excel doesn't list DATEDIF in its function menu or help, but it works in every version. If start_date is after end_date the result is #NUM!.
Syntax
=DATEDIF(start_date, end_date, unit)| Argument | What it means |
|---|---|
start_daterequired | The earlier date. |
end_daterequired | The later date. |
unitrequired | "Y" years, "M" months, "D" days, "YM" months left after whole years, "MD" days left after whole months, "YD" days since the last anniversary. |
Examples
| Formula | What it does | Result |
|---|---|---|
=DATEDIF(B2,C2,"Y") | Complete years of service. | 7 · 5 · 7 · 4 … |
=DATEDIF(B2,C2,"M") | Complete months. | 90 · 63 · 94 · 55 … |
=DATEDIF(B2,C2,"Y")&" yr "&DATEDIF(B2,C2,"YM")&" mo" | Years and months together. | 7 yr 6 mo · 5 yr 3 mo · 7 yr 10 mo · 4 yr 7 mo … |
=DATEDIF(B2,C2,"D") | Total days. | 2761 · 1918 · 2873 · 1690 … |
Common mistakes
Dates in the wrong orderstart_date must be the earlier date, or you get #NUM!.
"MD" quirksMicrosoft warns that "MD" can give odd results at month ends. Prefer "D" or "YM" when you can.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.