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:
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:
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:
To calculate a retirement date based on a birth date, you can use the EDATE function . In the example shown, the formula in D6 is: = EDATE ( C6 , 12 * 60 ) How this formula works The EDATE function is fully automatic, and will return a date xx...
If you want to calculated the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. Here are a few examples of the results that YEARFRAC...
To calculate and display the time between dates in days, months, and years, you can use the a formula based on the DATEDIF function. In the example shown, the formula in D6 is: = DATEDIF ( B6 , C6 , "y" ) & " years," &...
The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need current...
The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC to do things like calculate age with a birthdate.
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.