Exceljet

Quick, clean, and to the point

Get age from birthday

Excel formula: Get age from birthday
Generic formula 
=INT(YEARFRAC(birthdate,TODAY()))
Explanation 

To calculate a person's age from their birth date, you can use the YEARFRAC, INT, and TODAY functions. In the generic version of the formula above, birthdate is the person's birthday with year, and TODAY supplies the date on which to calculate age. Because TODAY always returns the current date, the formula will continue to calculate the correct age in the future as well.

In the example, the active cell contains this formula:

How the formula works

YEARFRAC calculates a decimal number representing the fraction of a year between two dates. To work out the fraction of a year as a decimal value, Excel uses whole days between two dates. This is straightforward in Excel, because all dates are simply serial numbers.

So, in this case, birthdate is supplied as the start_date from cell D4, and today's date is supplied as the end_date, courtesy of the TODAY function.

The result of YEARFRAC for Michael Chang, here in February 2016, is something like this:

14.7333333333333

Next, the INT function takes over and rounds down that number to the integer value, which is the number 14. Even though Michael is close to being 15 years old, he's still just 14 by the books, so this is the correct result.

If you want to calculate a person's age as of a given date, just replace the TODAY function with that date, or a cell reference to that date. See example below.

Adult or Minor

To check a birthday and return "Minor" or "Adult", you can wrap the age formula in the IF function like so:

=IF(INT(YEARFRAC(A1,TODAY()))<18,"Minor","Adult")

You can replace 18 with whatever age makes sense for your situation.

Age on specific date

To calculate age on a specific date, you can use the DATE function instead of the TODAY function like so:

=INT(YEARFRAC(A1,DATE(2020,1,1)))

Where A1 contains a birth date. This formula will return age on January 1, 2020.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.