If you need to calculate a person’s age from their birth date, you can do so with 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 this formula uses the TODAY function, it will continue to calculate the correct age in the future as well.
In the example, the active cell contains this formula:
Here’s how it 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.
In this case, birthdate is supplied as the start_date from cell A4, and today’s date is supplied as the end_date, courtesy of the TODAY function.
The result of this YEARFRAC for Thomas Tanaka in April 2013 is: 12.9458243692548
The INT function then rounds down that number to the integer value, which is the number 12. Even though Thomas is close to being 13 years old, he’s still just 12 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.