Formula 1: DATEDIF — the exact one
DATEDIF is Excel's worst-kept secret. It does not appear in the function list, but it has worked in every modern Excel and in Google Sheets for years. It counts complete units between two dates:
=DATEDIF(A2,TODAY(),"Y") gives full years. =DATEDIF(A2,TODAY(),"YM") gives leftover months after those years. =DATEDIF(A2,TODAY(),"MD") gives leftover days after those months. Three calls, one exact age.
Worked example. A2 holds 15 June 1990, and today is 15 October 2026. =DATEDIF(A2,TODAY(),"Y") returns 36. =DATEDIF(A2,TODAY(),"YM") returns 4. =DATEDIF(A2,TODAY(),"MD") returns 0. Thirty-six years, four months, zero days. Exact, leap days handled, no drama.
Formula 2: YEARFRAC — the decimal one
=YEARFRAC(A2,TODAY(),1) returns the age as a decimal number of years. For the same dates it gives about 36.33. The third argument, 1, tells Excel to count actual days, which keeps leap years honest.
Use this when you need a fraction, not a birthday-style answer: pro-rata calculations, precise tenure math, anything where "36 years and 4 months" is not granular enough. For anything a human will read, DATEDIF's years-months-days reads better.
Formula 3: the 30-second rough one
=INT((TODAY()-A2)/365.25). Subtract the dates to get days, divide by the average year length, chop the decimal. For our example it gives 36. Fast, memorable, and wrong near birthdays: the average smears leap days across years that never had them, so it can land a day off right when accuracy matters most.
Fine for trivia. Not fine for forms, payroll, or anything official. I explain why the average fails in How to Calculate Your Age in Days.
Combine them: one cell, full answer
String the three DATEDIF calls together and one cell reads like a person:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"
For 15 June 1990 that cell shows “36 years, 4 months, 0 days”. Copy it down a column of birth dates and you have an age list in seconds.
Three errors everyone hits
#NUM! means the start date is after the end date. Usually the "date of birth" cell holds a future date, or the arguments are backwards. DATEDIF(A2,TODAY(),...) needs the birth date first.
The second error is dates stored as text. If A2 looks like a date but formulas ignore it, Excel is seeing text, not a date. Select the column, use Text to Columns, and finish as a date. Or wrap it: =DATEDIF(DATEVALUE(A2),TODAY(),"Y").
The third is the American-versus-rest-of-world date order. 06/07/1990 is June 7 in the US and July 6 almost everywhere else. If ages come out a month off, check how Excel parsed the date, not the formula.
As of a specific date, not today
Replace TODAY() with the date you care about. =DATEDIF(A2,"2026-01-01","Y") gives the age on 1 January 2026. Put that target date in its own cell, say B2, and write =DATEDIF(A2,B2,"Y"). Now changing B2 recalculates every age in the sheet. This is how HR sheets answer “how old was everyone on the policy start date” without touching a single formula.