How to Calculate Age from a Birthdate in Excel

Calculate exact ages on a cutoff date with DATEDIF, place players in age divisions, and show age in years and months.

You will learn to

• Calculate age in complete years on a given date
• Use an age in a decision
• Show age as years and months

How it works

The reliable way to calculate age in Excel is =DATEDIF(birthdate, as_of, "Y"), which counts complete years — so a person is not a year older until their birthday has actually passed. Dividing the days by 365 drifts by a day every leap year and gives wrong ages around birthdays.

Put the as-of date in its own cell (a season cutoff, a policy date or a report date) instead of using TODAY(), so the results don’t change every day. =INT(YEARFRAC(birthdate, as_of, 1)) is an alternative, and DATEDIF with "YM" adds the leftover months.

=DATEDIF(B2,$I$1,"Y")