📎 This article includes 1 downloadable practice file ↓
HR needs completed years of service for gratuity eligibility, schools need a child’s age on 1 June, and a KYC check needs to know whether someone is 18 yet. All of these come down to the difference between two dates in years, months and days — and Excel’s best tool for it, DATEDIF, is hidden (it isn’t in the function list or autocomplete, but it works in every version).
In this article
Completed years
=DATEDIF(B2, TODAY(), "y")B2 is the date of birth or joining date. "y" returns whole completed years — someone born on 10-Oct-1990 is 35 until 9-Oct-2026 and turns 36 on 10-Oct-2026.
Years, months and days as text
=DATEDIF(B2,C2,"y") & " years " & DATEDIF(B2,C2,"ym") & " months " & DATEDIF(B2,C2,"md") & " days"| Unit | Returns |
|---|---|
"y" |
Completed years |
"m" |
Completed months in total |
"d" |
Total days |
"ym" |
Months left after whole years |
"md" |
Days left after whole months |
"yd" |
Days left after whole years |
"md" can give wrong results around month-ends (for example 31-Jan to 1-Mar). For exact day counts in legal or payroll work, calculate days separately: =C2 - EDATE(B2, DATEDIF(B2,C2,"m")).Age on a specific date
=DATEDIF(B2, DATE(2026,6,1), "y") ' age on 1 June 2026Decimal years with YEARFRAC
=ROUND(YEARFRAC(B2, TODAY(), 1), 2) ' e.g. 7.43 yearsBasis 1 uses actual days, which is the most accurate for ages. Good for averages (“average tenure 4.8 years”) where text output is useless.
Gratuity-style completed years (rounding above 6 months)
Under India’s gratuity rules, service of more than six months in the final year is commonly counted as a full year. A helper for that rounding:
=DATEDIF(B2,C2,"y") + (DATEDIF(B2,C2,"ym") >= 6)Check your organisation’s policy and current law before using it for payouts — this formula only handles the rounding.
Is the person 18 or older?
=IF(DATEDIF(B2, TODAY(), "y") >= 18, "Adult", "Minor")
=EDATE(B2, 12*18) ' the date they turn 18Where people go wrong
- #NUM! — the start date is after the end date. Swap them or use
MIN/MAX. - Dividing days by 365 —
(C2-B2)/365drifts because of leap years. Use DATEDIF or YEARFRAC. - Text dates — “10/10/1990” typed on a PC with US settings becomes 10-Oct or doesn’t convert at all. Check with
ISNUMBER(B2). - TODAY() changes every day — for a report “as on 31 March”, use a fixed date cell instead.
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.