Excel Age From Date of Birth Formula
By Szabó Gergő · Updated
DATEDIF with unit Y returns whole years between a birth date and today, which is the usual age in years.
Syntax and arguments
=DATEDIF(birth_date,TODAY(),"Y")- birth_date
- A real Excel date, not text that looks like a date.
- end_date
- Usually TODAY(), or a fixed as-of date.
Age from birth date examples
Birth date in A2.
=DATEDIF(A2,TODAY(),"Y")Returns completed years only. Someone who turns 30 tomorrow is still 29.
As-of date in C1.
=DATEDIF(A2,$C$1,"Y")Locks the report date so every row uses the same as-of day.
Need more detail.
=DATEDIF(A2,TODAY(),"Y")&"y "&DATEDIF(A2,TODAY(),"YM")&"m"YM is leftover months after whole years.
Common mistakes
Using YEAR(TODAY())-YEAR(A2)
That ignores the birthday. A January birth in December is still not a full extra year.
Future birth dates
Guard with =IF(A2>TODAY(),"",DATEDIF(A2,TODAY(),"Y")).
Text dates from a CSV
Convert with DATEVALUE before DATEDIF.
Age from birth date FAQ
Is DATEDIF documented?
It is undocumented in recent Excel help but still works. YEARFRAC is the documented alternative.
How do I get age as a decimal?
=YEARFRAC(A2,TODAY(),1) uses actual/actual day count.
Can I compute next birthday?
=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY()),MONTH(A2),DAY(A2)).