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 (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute identically in every LibreOffice version we test (desktop Excel behavior per docs — we do not run desktop Excel; Google Sheets is executed separately, see each function page) — 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). Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 11 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
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.
How-to recipes using these functions
- How to calculate age in years from a birth date
- How to calculate age in years and months
- How to calculate elapsed years as a decimal (YEARFRAC)
- How to calculate the number of days between two dates
- How to calculate the number of months between two dates
- How to calculate the next birthday (or anniversary) date