Because TODAY always returns the current date, the formula will continue to calculate the correct age in the future. In the example shown, the goal is to calculate age in years. The formula in E5 is: The first two arguments for DATEDIF are start_date and end_date. The start date comes from cell D5 (May 15, 2001) in the example. The end date is generated with the TODAY function. TODAY always returns the current date in Excel. As of this writing, the current date is November 24, 2020. The last argument in DATEDIF specifies the time unit. The DATEDIF function supports several options here, but for this example the goal is age in whole years, so we use “y” to specify complete years. At this point, we can rewrite the formula as below: Because Excel dates are actually serial numbers, the raw values are: With these inputs, DATEDIF returns 19 as a final result.
Age on specific date
To calculate age on a specific date, replace the TODAY function with the target date. An easy and safe way to hardcode a specific date into a formula is to use the DATE function. For example, to calculate age as of January 1, 2021, you can use a formula like this: This formula will return Michael Chang’s age on January 1, 2022, which is 20.
Adult or Minor
To check a birthdate and return “Minor” or “Adult”, you can wrap the formula in the IF function like so: The above formula is an example of nesting. Replace 18 with whatever age is appropriate.
Age in years, months, and days
To calculate age in years, months, and days, use three instances of DATEDIF like this: The first instance of DATEDIF returns years, the second instance returns months, and the third instance returns days. This is an example of concatenation, and the result is a text string like this: Note start and end dates remain the same in all three DATEDIFs; only the unit is changed.
YEARFRAC with INT
Another option for calculating age from birthdate uses the YEARFRAC function together with the INT function in a formula like this: 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 days between two dates. As above, the birthdate is provided as the start_date from cell D5, and today’s date is supplied as the end_date, courtesy of the TODAY function. With a current date of November 24, 2020, the result from YEARFRAC for Michael Chang is: Next, the INT function takes over and rounds down that number to the integer value, which is the number 19. This formula appears perfectly logical and it works fine in most cases. However, YEARFRAC can return a number that isn’t correct on anniversary dates (birthdays). I’m not sure exactly why this happens, but it is related to how YEARFRAC uses days to determine fractional years, which is controlled by the basis argument. For example: The bottom line is that the date DATEDIF formula is a safer and easier option when the goal is to report age in whole years.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
