DATEDIF function
DATEDIF counts the complete years, months or days between two dates. It is the standard way to calculate age or length of service. It works in every version of Excel, but it is undocumented: it doesn't appear in the function list or autocomplete, so you have to type it in full.
- Available in
- Every current version
- Category
- Date functions
- Returns
- A whole number of complete units.
- Google Sheets
- Works the same in Google Sheets, where it is documented.
Syntax
=DATEDIF(start_date, end_date, unit)
| Argument | Required? | What it is |
|---|---|---|
start_date | Required | The earlier date. |
end_date | Required | The later date. If it is before start_date, DATEDIF returns #NUM!. |
unit | Required | "Y" complete years, "M" complete months, "D" days, "YM" months after the whole years, "MD" days after the whole months, "YD" days after the whole years. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =DATEDIF(B2,C2,"Y") | 7 | Ana has 7 complete years |
| E3 | =DATEDIF(B3,C3,"M") | 22 | Raj has 22 complete months; the 23rd isn't finished |
| E4 | =DATEDIF(B2,C2,"Y")&" yrs, "&DATEDIF(B2,C2,"YM")&" mos" | 7 yrs, 6 mos | Years plus leftover months, as a label |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | Start date | As of | Result | |
| Ana | 4/15/2019 | 10/28/2026 | 7 | |
| Raj | 11/30/2024 | 10/28/2026 | 22 | |
| 7 yrs, 6 mos |
Practice DATEDIF
Graded instantly, then re-checked on changed data.
Years and months of service
C2, calculate each employee's completed years of service as of the date in G1. In D2, return the months left over after those years. Fill both down to row 6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NUM! | The start date is after the end date. Swap them so the earlier date comes first. |
| #NUM! or #NAME? with a valid pair of dates | The unit is wrong. It must be one of "Y", "M", "D", "YM", "MD" or "YD" in quotes; a misspelled unit gives #NUM! and a unit without quotes gives #NAME?. |
| Can't find it in the function list | DATEDIF is undocumented in Excel, so autocomplete won't offer it. Type the whole formula; it still works. |
| Odd results with "MD" | Microsoft warns that the "MD" unit can return wrong values near month ends. Prefer "Y" and "YM", or subtract dates for days. |
Tips
- Use TODAY() as the end date to keep ages and tenure current: =DATEDIF(A2,TODAY(),"Y").
Questions
How do I calculate age in Excel?
Use =DATEDIF(birth_date,TODAY(),"Y"). It counts complete years, so the age only goes up on the birthday.
Why can't I find DATEDIF in Excel?
It is an undocumented compatibility function. It doesn't show in autocomplete or the Insert Function dialog, but typing it works in every version.
How do I show years and months of service?
Combine two DATEDIF calls: =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months".
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.