How to Calculate Age in Excel From a Date of Birth
Calculate age in Excel from a birth date with DATEDIF or YEARFRAC, show years and months, age on a specific date, and avoid the divide-by-365 mistake.
By Ayoub Farid · · 1 min read
There's no AGE function in Excel, but one short formula does the job. Birth dates are in column B.
Age in years
=DATEDIF(B2,TODAY(),"y")Returns complete years, so someone born 20 March 2000 is 25 until 20 March 2026, then 26. See DATEDIF.
Alternative: YEARFRAC
=INT(YEARFRAC(B2,TODAY(),1))YEARFRAC returns fractional years; INT drops the decimals.
Why not divide by 365?
=(TODAY()-B2)/365 ignores leap years and can be off by a day around birthdays. Use DATEDIF or YEARFRAC.
Years and months
=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months"Age on a specific date
Age at the start of the school year (date in F1):
=DATEDIF(B2,$F$1,"y")Lock F1 with $ before filling down. See absolute references.
Age groups
=IFS(C2<18,"Under 18",C2<35,"18–34",C2<55,"35–54",TRUE,"55+")Then count each group with COUNTIF.
Common problems
- #NUM!: the birth date is after the end date (or a typo year like 2099).
- #VALUE!: the "date" is stored as text. Check with
=ISNUMBER(B2). - Result looks like a date: format the age cell as Number.
Frequently asked questions
What is the formula to calculate age in Excel?
=DATEDIF(birthdate,TODAY(),"y").
How do I calculate age in years and months?
Combine DATEDIF with "y" and "ym" units.
Why does my age formula return a date?
The cell is formatted as a date. Change it to Number or General.
Does this work in Google Sheets?
Yes, DATEDIF and YEARFRAC both work.