Why does DATEDIF show #NAME? every time you type it? Why does Excel’s Formula AutoComplete ignore it completely? Why does your colleague swear it works on their laptop but yours throws an error?
The answer is simple: DATEDIF isn’t broken — it’s buried. Microsoft never removed it. They just stopped documenting it after Excel 2007, and quietly disabled IntelliSense support. It’s still there. You’re just not typing it right — or using the right syntax for your version and locale.
The Setup
You’re auditing employee tenure at BlueSky Logistics, a midsize freight company in Shenzhen. HR sent you a raw export from their HRIS — no formulas, just names, hire dates, and termination status. Your task: calculate years of service for active staff and flag anyone with >5 years for promotion review. The data lives in A1:C10.
| A | B | C |
|---|---|---|
| Name | Hire Date | Status |
| Liu Wei | 2019-06-12 | Active |
| Zhang Mei | 2017-11-03 | Active |
| Chen Tao | 2020-02-28 | Active |
| Wang Lin | 2015-09-17 | Active |
| Xu Yan | 2021-04-05 | Terminated |
| Sun Jie | 2016-07-22 | Active |
| Feng Yi | 2018-12-14 | Active |
| Luo Hui | 2022-08-30 | Active |
The Challenge
You need clean, human-readable tenure: “3 years”, “5 years 4 months”, or “1 year 11 days”. Not decimal years. Not serial numbers. And it must auto-update as today’s date changes.
DATEDIF seems perfect — but when you type =DATEDIF(B2,TODAY(),"y") in D2 and hit Enter, Excel returns #NAME?. You check spelling. You toggle formula autocomplete (Alt+M, then M). Nothing. You paste the exact same formula into your manager’s file — and it works. What gives?
Here’s what most people miss: DATEDIF only works if all three arguments are valid and the unit code is uppercase. Lowercase "y"? #NAME?. Mixed case "Ym"? #NUM!. Also — and this trips up 70% of users — if the start date is later than the end date, DATEDIF fails silently or returns #NUM!, not #VALUE!.
Walking Through It
Let’s fix Liu Wei first. In cell D2, enter:
=DATEDIF(B2,TODAY(),"Y")
Note: "Y", not "y". Press Enter. You’ll see 5 — correct, since today is 2024-06-12.
Now try months: in E2, type:
=DATEDIF(B2,TODAY(),"YM")
That’s "YM" — uppercase, no spaces. You’ll get 0 (since June 12 minus June 12 = zero months beyond full years).
For days beyond that: in F2, type:
=DATEDIF(B2,TODAY(),"MD")
Result: 0. So Liu Wei has exactly 5 years.
Now test Zhang Mei (B3 = 2017-11-03). D3 = =DATEDIF(B3,TODAY(),"Y") → 6. E3 = =DATEDIF(B3,TODAY(),"YM") → 7. F3 = =DATEDIF(B3,TODAY(),"MD") → 9. That’s 6 years, 7 months, 9 days.
But here’s the counterintuitive tip: Never use "MD" with dates near month ends. Try it on Chen Tao (2020-02-28) — you’ll get #NUM! because TODAY() is June 12, and February 28 + 3 months ≠ May 28. Excel can’t compute day difference across irregular month lengths. Use "D" instead for total days — then convert manually if needed.
| Before (D2:F2) | After (D2:F2) | Notes |
|---|---|---|
| #NAME? | 5 | Fixed case: "Y" not "y" |
| #NAME? | 0 | "YM" — uppercase, no space |
| #NAME? | 0 | "MD" works only when day-of-month is safe |
The Result
After applying DATEDIF correctly to rows 2–10, column D shows full years, E shows remaining months, F shows remaining days — but only where safe. For reliability, we switch to a hybrid approach in column G: a clean text string built with CONCATENATE (or &).
| Name | Years | Months | Tenure (Text) |
|---|---|---|---|
| Liu Wei | 5 | 0 | 5 years |
| Zhang Mei | 6 | 7 | 6 years 7 months |
| Chen Tao | 4 | 3 | 4 years 3 months |
| Wang Lin | 8 | 8 | 8 years 8 months |
| Sun Jie | 7 | 10 | 7 years 10 months |
| Feng Yi | 5 | 5 | 5 years 5 months |
| Luo Hui | 1 | 9 | 1 year 9 months |
What Could Go Wrong
Mistake #1: Using lowercase unit codes
Typing "y" instead of "Y" triggers #NAME? — even though Excel accepts other functions in any case. DATEDIF is case-sensitive. No warning. Just silence and failure.
Mistake #2: Assuming DATEDIF handles leap years flawlessly
It doesn’t. Try DATEDIF("2020-02-29","2021-02-28","Y"). Returns 0 — but that’s misleading. Someone hired Feb 29, 2020, worked until Feb 28, 2021, has served 365 days — not zero years. Excel treats Feb 29 as Mar 1 in non-leap years.
Mistake #3: Nesting DATEDIF inside IF without testing edge cases
Like =IF(C2="Active",DATEDIF(B2,TODAY(),"Y"),""). Looks safe — until B2 is blank. Then DATEDIF returns #NUM!, not blank. Wrap it: =IF(OR(B2="",C2<>"Active"),"",DATEDIF(B2,TODAY(),"Y")).
Here’s what to do next — copy-paste these four safe alternatives into your workbook right now:
| Use Case | Formula | Shortcut Tip |
|---|---|---|
| Exact years (rounded down) | =DATEDIF(B2,TODAY(),"Y") | Alt+M, M → type manually |
| Total months | =(YEAR(TODAY())-YEAR(B2))*12+MONTH(TODAY())-MONTH(B2)+(DAY(TODAY())| No DATEDIF needed | |
| Clean "X years Y months" text | =IF(DATEDIF(B2,TODAY(),"Y")=1,DATEDIF(B2,TODAY(),"Y")&" year "&DATEDIF(B2,TODAY(),"YM")&" months",DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months") | Ctrl+Enter to fill down fast |
| Days between dates (safe) | =TODAY()-B2 | Always works. No units to mis-type. |
