Need an Excel Formula 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.

 

Fred Pryor Seminars_Excel Formula to Calculate Age 1

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.

Fred Pryor Seminars_Excel Formula to Calculate Age 2

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.

Fred Pryor Seminars_Excel Formula to Calculate Age 3

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.

Local Seminars Related to this Topic:

Related Formulas Articles

Categories

Monthly Archives

No Comments

  • Believe says:

    =(Today()-start date)/360.25
    In most cases you would be asked to Round Down or Up your answer then you would have to do it like this =ROUNDDOWN((Today()-start date)/360.25,0).
    Remember that you can also use TRUNC instead of ROUND

  • Jamaldeen says:

    someone help me, how about a case when u have more than one date of births in one cell

  • pawan kumar says:

    Excel fourmula

Leave a Reply

Your email address will not be published. Required fields are marked *