Get age from birthday

The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the interval between two dates in years, months, and days.

In the example shown, the goal is to calculate age in years. The formula in E5 is:

=DATEDIF(D5,TODAY(),"y")

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:

=DATEDIF("15-May-2001","24-Nov-2020", "y")

Because Excel dates are actually serial numbers, the raw values are:

=DATEDIF(37026,44159,"y")

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:

=DATEDIF(D5,DATE(2022,1,1),"y") // returns 20

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:

=IF(DATEDIF(D5,TODAY(),"y")<18,"Minor","Adult")

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:

=DATEDIF(A1,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"

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:

19y 6m 9d

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:

=INT(YEARFRAC(D5,TODAY()))

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:

19.5290896646133

Next, the INT function takes over and rounds down that number to the integer value, which is the number 19.

=INT(19.5290896646133) // returns 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:

=YEARFRAC(DATE(1960,6,30),DATE(1962,6,30),1) // 1.998, should be 2
=YEARFRAC(DATE(1960,3,3),DATE(1964,3,3),1) // 3.998, should be 4

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.