How to / Dates
How to calculate age from a date of birth in Excel
Use =DATEDIF(B2, C2, "Y"), with the date of birth in B2 and the date to measure to in C2. For today, use TODAY() in place of C2.
Age in complete years
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Born | On | ||
| 2 | Ana | 1990-05-20 | 2026-03-01 | 35 | |
| 3 | Ben | 2000-03-01 | 2026-03-01 |
In E2: =DATEDIF(B2, C2, "Y") → 35
Born 20 May 1990; on 1 March 2026 the 36th birthday has not come yet.
How it works
- DATEDIF counts the complete units between two dates.
"Y"asks for complete years, so the age only goes up on the birthday.- Excel does not list DATEDIF when you start typing it, but it works in every version.
Variations
On the birthday itself
A B C D E 1 Name Born On 2 Ana 1990-05-20 2026-03-01 26 3 Ben 2000-03-01 2026-03-01 In E2:
=DATEDIF(B3, C3, "Y")→ 26Ben was born on 1 March 2000, and turns 26 on the day in C3.
Years and months
A B C D E 1 Name Born On 2 Ana 1990-05-20 2026-03-01 35 years, 9 months 3 Ben 2000-03-01 2026-03-01 In E2:
=DATEDIF(B2, C2, "Y")&" years, "&DATEDIF(B2, C2, "YM")&" months"→ 35 years, 9 months"YM" is the months left over after the complete years.
With YEARFRAC
A B C D E 1 Name Born On 2 Ana 1990-05-20 2026-03-01 35 3 Ben 2000-03-01 2026-03-01 In E2:
=INT(YEARFRAC(B2, C2, 1))→ 35Age in complete months
A B C D E 1 Name Born On 2 Ana 1990-05-20 2026-03-01 429 3 Ben 2000-03-01 2026-03-01 In E2:
=DATEDIF(B2, C2, "M")→ 429
Watch out for
- Subtracting the years.
=YEAR(C2)-YEAR(B2)gives 36 here, a year too many: it ignores whether the birthday has passed. - Dividing the days by 365.
=(C2-B2)/365drifts by a day for every leap year, and is wrong around the birthday. - #NUM! DATEDIF needs the earlier date first. A date of birth after the end date gives #NUM!.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- DATEDIF: The difference between two dates in years, months or days.
- TODAY: Today's date. Updates automatically.
- YEARFRAC: The fraction of a year between two dates, using a day-count basis.
- INT: Rounds down to the nearest whole number.
- YEAR: The year of a date.