How to Use DATEDIF in Excel
Returns the difference between two dates in complete years, months or days — the standard way to calculate age and tenure.
What is DATEDIF in Microsoft Excel?
DATEDIF measures the gap between two dates in a unit you choose: "Y" for whole years, "M" for whole months, "D" for days. It is a legacy function that Excel keeps hidden from the function list but still supports, and it remains the cleanest way to compute employee tenure and customer age.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| start_date | Required | The earlier date. Must be on or before end_date. |
| end_date | Required | The later date, often TODAY(). |
| unit | Required | "Y" whole years, "M" whole months, "D" days, "YM" leftover months, "MD" leftover days, "YD" leftover days ignoring years. |
When to Use DATEDIF (Real-World Business Scenarios)
- 1
Calculating exact age in years from a date of birth with unit "Y".
- 2
Working out completed months of service for a probation or notice period.
- 3
Building tenure buckets for an HR dashboard without nesting YEAR and MONTH.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Employee | Joining Date | Tenure (Years) |
|---|---|---|
| Rahul | 2019-06-15 | 7 |
| Pooja | 2022-01-10 | 4 |
| Aman | 2025-03-01 | 1 |
How it works: Counts only completed years between the joining date and today. A person who joined in June 2019 has 7 full years of service in November 2026.
Common Errors & How to Fix Them
Why it happens: The start date is later than the end date, which DATEDIF cannot express.
Fix: Swap the arguments or guard with IF(B2>C2,0,DATEDIF(B2,C2,"Y")).
Why it happens: DATEDIF is a compatibility function and does not appear in the function wizard.
Fix: Type it manually — it still works in every Excel version and in Google Sheets.
Try DATEDIF Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (DATEDIF)
What does the DATEDIF formula do in Excel?
DATEDIF measures the gap between two dates in a unit you choose: "Y" for whole years, "M" for whole months, "D" for days. It is a legacy function that Excel keeps hidden from the function list but still supports, and it remains the cleanest way to compute employee tenure and customer age.
What is the syntax for DATEDIF?
What are common mistakes when using DATEDIF?
#NUM!: The start date is later than the end date, which DATEDIF cannot express. (Swap the arguments or guard with IF(B2>C2,0,DATEDIF(B2,C2,"Y")).) Function missing from autocomplete: DATEDIF is a compatibility function and does not appear in the function wizard. (Type it manually — it still works in every Excel version and in Google Sheets.)
Related Excel Formulas
Keep building your formula toolkit with these date & time functions.
- TODAYReturns the current date, refreshed every time the workbook recalculates — the anchor for ageing reports.
- EOMONTHReturns the last day of a month, any number of months before or after a given date — the backbone of monthly reporting.
- NETWORKDAYSCounts working days between two dates while excluding weekends and your own holiday list.
- EDATEAdds or subtracts whole months from a date while keeping the same day of the month — perfect for due dates and renewals.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
Master DATEDIF in Intermediate Excel
Learn DATEDIF along with our formula library, practice files, and verifiable certificates in our complete curriculum.