How to Calculate Age From a Birthday in Google Sheets
Type =DATEDIF(B2,TODAY(),"Y") to turn a date of birth in B2 into a whole-number age that updates itself every day.
=DATEDIF(B2,TODAY(),"Y")
DATEDIF measures the gap between the birthday and today; "Y" returns completed years. TODAY() keeps it current on every open.
How it works
DATEDIF is a hidden function (it will not appear in autocomplete) but it is the cleanest way to get an age. It takes a start date, an end date, and a unit code. Use TODAY() as the end date so the age recalculates automatically. Make sure the birthday cell is a real date, not text — if it is left-aligned it is text, and DATEDIF will return #VALUE!.
Variations
Age in whole months
=DATEDIF(B2,TODAY(),"M")
"M" returns total completed months.
Exact age: years, months, days
=DATEDIF(B2,TODAY(),"Y")&"y "&DATEDIF(B2,TODAY(),"YM")&"m "&DATEDIF(B2,TODAY(),"MD")&"d"
Combines three DATEDIF units into one readable string.
Age at a specific date
=DATEDIF(B2,DATE(2025,1,1),"Y")
Swap TODAY() for a fixed DATE() to freeze the age.
Examples
| Scenario | Formula |
|---|---|
| Age today from DOB in B2 | =DATEDIF(B2,TODAY(),"Y") |
| Age on their next review date in C2 | =DATEDIF(B2,C2,"Y") |
FAQ
Why does DATEDIF return #VALUE!?
The birthday cell is text, not a date. Retype it as a real date (e.g. 1990-05-14) or wrap it in DATEVALUE: =DATEDIF(DATEVALUE(B2),TODAY(),"Y").
Why is the age one year too high or low?
DATEDIF with "Y" counts completed years, so it only ticks up on the birthday itself. That is correct behaviour, not an error.
Can I get the age automatically for a whole column?
Yes, drag the formula down, or use =ARRAYFORMULA(DATEDIF(B2:B,TODAY(),"Y")) to fill the column in one cell.