Using the Age Formula in Excel to Calculate Age

Have you ever needed to calculate a person’s age from a birthdate in Excel? Or find the number of years since the last audit?

Excel includes two functions that perform well: YEARFRAC and DATEDIF.

The YEARFRAC Function

YEARFRAC gives the number of years between two dates. The FRAC is short for fraction, because this function returns a number with a decimal representing the fractional portion of an incomplete year.

YEARFRAC has an additional optional parameter for the method of calculating years. Many financial transactions assume that each month has thirty days and that each year has 360 days. Excel uses this “30/360” as the default method of calculating the number of years. In most cases this works well enough. However, if you prefer to use the actual number of days in each year, enter “1” for the optional third parameter.

Note the .24722222 on the end of the number. This represents approximately a quarter of a year beyond the thirty-four-year age. If you want only the number of complete years, wrap the formula in a TRUNC function: “=TRUNC(YEARFRAC(B2,B3))” returns an even thirty-four.

The DATEDIF Function

The DATEDIF function was included originally to be compatible with Lotus 1-2-3. These days few people are looking for 1-2-3 compatibility, but the function has become popular in its own right.

The “y” for the third parameter instructs Excel to count the number of years.

DATEDIF is more flexible than YEARFRAC. It calculates the number of years, months, or days, depending on the third parameter—“y” to calculate years, “ym” for months left over after the years are counted, and “md” for days left over after the years and months are counted.

This indicates a total of thirty-four years, two months, and thirty days between 1/22/1980 and 4/21/2014.

Combining with the TODAY Function

The examples above show calculations using two fixed dates. If instead you need the number of years between a date and the current date, replace one date with the TODAY function. In the DATEDIF example above, try entering “=DATEDIF(B2,TODAY(),”y”)”.

Warning about Text Formats

On the Home ribbon, in the Number group, check the format of the cell in which you have entered your date. Be sure it is set to a date format, not formatted as text. Text values that contain dates, but are not formatted as dates, can cause problems in Excel’s calculations.

Next Steps

Excel’s date and time system is robust and includes many functions for calculating and comparing dates and times. Be sure to check the “Date and Time Functions” section of Excel’s help menus for more functions that you can use.