DATEDIF calculates exact age, tenure, and duration between dates. The hidden formula Excel professionals rely on — here is the complete guide.
DATEDIF calculates the difference between two dates in years, months, or days. It is the most reliable way to calculate exact ages, lengths of service, contract durations, and time elapsed in Excel.
DATEDIF is unusual because it is a hidden function — it does not appear in autocomplete and is not documented in the Formula Builder. But it works in every version of Excel and is used by professionals daily.
=DATEDIF(start_date, end_date, unit)| Argument | Description |
|---|---|
| start_date required | The earlier date. Must be before end_date or DATEDIF returns an error. |
| end_date required | The later date. Use TODAY() for calculations up to the current date. |
| unit required | The unit to return. See the table below. |
| Unit | Returns | Example |
|---|---|---|
| "Y" | Complete years between dates | 3 (for 3 years and 8 months) |
| "M" | Complete months between dates | 44 (total months) |
| "D" | Total days between dates | 1340 |
| "MD" | Days ignoring months and years | 15 (days past the month) |
| "YM" | Months ignoring years | 8 (months past the year) |
| "YD" | Days ignoring years | 245 (days into the current year) |
=DATEDIF(A2, B2, "D")Returns the exact number of days from start date to end date. You can also just use =B2-A2 for days — DATEDIF shines more for years and months.
=DATEDIF(A2, TODAY(), "Y")Returns how many complete years an employee has worked. If they started 3 years and 11 months ago, this returns 3 — only complete years count.
=DATEDIF(TODAY(), B2, "M")How many complete months until the contract end date. Put TODAY() as start_date and the end date as end_date to look forward.
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months"
Produces "32 years, 4 months". The "YM" unit returns the months past the most recent whole year.
=DATEDIF(A2, TODAY(), "D")&" days"Produces "147 days" — a live counter that updates every day the file is opened.
=DATEDIF(A2,TODAY(),"Y")&"y "&
DATEDIF(A2,TODAY(),"YM")&"m "&
DATEDIF(A2,TODAY(),"MD")&"d"
Produces "3y 8m 14d" — the most precise way to show tenure. HR teams use this for long-service awards and redundancy calculations.
This is how Excel professionals calculate someone's exact age from their date of birth:
=DATEDIF(A2, TODAY(), "Y")
← A2 = date of birth. Returns complete years.
Dividing by 365 is slightly inaccurate due to leap years. DATEDIF("Y") correctly handles birthdays that have or haven't occurred yet this year, giving the right age every time.
DATEDIF returns #NUM! if start_date is later than end_date. Always check your argument order — start_date first, end_date second.
DATEDIF is not in Excel's function library autocomplete. You must type the full name correctly. If it doesn't work, check your spelling — it is DATEDIF not DATEDIFF (one F not two).
The "MD" unit (days ignoring months and years) can return incorrect results in some date combinations. Microsoft acknowledges this. For day-level precision, use "D" (total days) and subtract the year and month components manually.
ExcelPro has DATEDIF, NETWORKDAYS, WORKDAY and EOMONTH exercises in every track. Free to start.
Try date exercises →