Question

Excel formula for find the age of a person for a given date of birth in number of years, months and days

Answer 1

$<marquee>$
█ █ █ █ █ █ █$\colorbox{black}{}$$\colorbox{green}{}$$\colorbox{black}{ }$$\colorbox{pink}{ }$$\colorbox{orange}{ }$$\colorbox{red}{ }$$\colorbox{yellow}{ }$$\colorbox{black}{ }$$\colorbox{purple}{ }$$\colorbox{teal}{ }$$\colorbox{brown}{ }$$\colorbox{blue}{ }$$\colorbox{black}{ }$$\colorbox{voilet}{ }$$\colorbox{red}{ }$$\colorbox{cyan}{ }$$\colorbox{green}{ }$
█ █ █ █ █ █ █$\colorbox{black}{}$$\colorbox{green}{}$$\colorbox{black}{ }$$\colorbox{pink}{ }$$\colorbox{orange}{ }$$\colorbox{red}{ }$$\colorbox{yellow}{ }$$\colorbox{black}{ }$$\colorbox{purple}{ }$$\colorbox{teal}{ }$$\colorbox{brown}{ }$$\colorbox{blue}{ }$$\colorbox{black}{ }$$\colorbox{voilet}{ }$$\colorbox{red}{ }$$\colorbox{cyan}{ }$$\colorbox{green}{ }$$<marquee>$
█ █ █ █ █ █ █ $\colorbox{black}{}$$\colorbox{green}{}$$\colorbox{black}{ }$$\colorbox{pink}{ }$$\colorbox{orange}{ }$$\colorbox{red}{ }$$\colorbox{yellow}{ }$$\colorbox{black}{ }$$\colorbox{purple}{ }$$\colorbox{teal}{ }$$\colorbox{brown}{ }$$\colorbox{blue}{ }$$\colorbox{black}{ }$$\colorbox{voilet}{ }$$\colorbox{red}{ }$$\colorbox{cyan}{ }$$\colorbox{green}{ }$

Answer 2

Generic formula 

=INT(YEARFRAC(birthdate,TODAY()))

Explanation 

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:

=INT(YEARFRAC(D4,TODAY()))

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.

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, given a birth date, you can use the DATE functioninstead of the TODAY function like so:

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

Where A1 contains a birth date.