The DATEDIF function in Excel
DATEDIF measures the gap between two dates in complete years, months or days. It's the standard way to work out employee tenure, a customer's age or how long a subscription ran. It's a "hidden" function that Excel doesn't autocomplete, but it works in every version and turns up regularly in HR and finance tests.
Syntax
=DATEDIF(start_date, end_date, unit)
| start_date | The earlier date. |
|---|---|
| end_date | The later date. If it's before start_date, you get #NUM!. |
| unit | What to count, in quotes: "Y" full years, "M" full months, "D" days, "YM" months left over after full years, "MD" days left over after full months, "YD" days left over after full years. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In D2, use DATEDIF to calculate how many complete months the customer was subscribed, from the start date in B2 to the end date in C2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Months | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: Start date first, end date second, then the unit for whole months in quotes.
Examples
=DATEDIF(B2,C2,"Y")→ 5Counts the complete years between Ana's hire date and her review. A year only counts once it's fully finished.
=DATEDIF(B3,C3,"M")→ 40Counts complete months instead, which is better for short tenures like Ben's.
=DATEDIF(B4,C4,"Y")&" years, "&DATEDIF(B4,C4,"YM")&" months"→ "8 years, 8 months"Combines two units into a readable label: full years, then the months left over after those years.
Example data
| 1 | Employee | Hire date | Review date |
|---|---|---|---|
| 2 | Ana Ruiz | 4/15/2019 | 3/31/2025 |
| 3 | Ben Okoro | 11/1/2021 | 3/31/2025 |
| 4 | Chloé Martin | 7/20/2016 | 3/31/2025 |
Common mistakes
Start and end dates swapped
DATEDIF returns #NUM! when the start date is later than the end date. The earlier date always goes first.
Unit without quotes
DATEDIF(B2,C2,Y)returns #NAME?. The unit is text:"Y".Dates stored as text
A date typed as text, such as one imported from a CSV, returns #VALUE!. Convert it to a real date, or build one with
DATE(year,month,day).Trusting the "MD" unit
"MD" can give odd results around month ends. For leftover days, subtract dates directly or use
"D"for total days.
FAQ
How do I calculate age in Excel?+
Use the birth date and today's date: =DATEDIF(B2,TODAY(),"Y"). It returns completed years, so the age only goes up on the birthday.
Why can't I find DATEDIF in Excel's function list?+
It's kept for compatibility with older spreadsheet programs, so Excel doesn't suggest it or show tooltips. Type the full formula and it works.
How do I get the number of days between two dates?+
Subtract them, =C2-B2, or use =DAYS(C2,B2). DATEDIF(B2,C2,"D") gives the same answer.
Lesson
Master DATEDIF step by step: Days between dates: DAYS, DATEDIF and TODAY
Measure the gap between two dates in days, months and years, and calculate tenure as of today.