DATEDIF vs YEARFRAC: whole units or a fraction
Both measure the distance between two dates in year-terms, but they answer different questions: DATEDIF counts COMPLETED calendar units (a person is 34 until their birthday), YEARFRAC returns a DECIMAL (34.53 years) under a chosen day-count convention. Ages want the first; accruals and prorating want the second.
The differences at a glance
| DATEDIF | YEARFRAC | |
|---|---|---|
| Returns | Whole completed years / months / days (by unit code) | Decimal years |
| Unit codes / basis | "Y", "M", "D", plus remainders "YM", "MD", "YD" | Basis 0 = US 30/360 (default!), 1 = actual/actual, 3 = actual/365... |
| Birthday semantics | Correct — increments ON the anniversary | 34.9972 the day before; needs INT() and care |
| Documentation | Undocumented in Excel's wizard (Lotus legacy) but fully working | Documented everywhere |
| Compatibility | Universal — verified despite the missing docs | Universal (verified incl. basis differences) |
Which should you use?
- DATEDIF — Human-calendar answers: ages, tenure as '34 years, 5 months' (Y + YM), countdown displays. Avoid the "MD" unit for month-end edge cases — it has documented quirks in Excel itself.
- YEARFRAC — Money math over time: interest accruals, prorated fees, service-fraction calculations — choosing the basis deliberately (1 for real calendars, 0/30-360 only when a contract says so).
Compatibility (from executed tests)
Both execute identically in every version of all three apps we test — DATEDIF works fine everywhere despite Excel never listing it in the function wizard, and YEARFRAC's basis codes agree cross-app (verified: basis 0 gives exactly 0.5 for Jan 1 → Jul 1; basis 1 gives 0.4959).
Example formulas
| Exact age in years | =DATEDIF(A2,TODAY(),"Y") |
| Years and months | =DATEDIF(A2,B2,"Y")&"y "&DATEDIF(A2,B2,"YM")&"m" |
| Accrued fraction of a year (real calendar) | =YEARFRAC(A2,B2,1) |
Full per-version details on each function page: DATEDIF · YEARFRAC.