Age Calculation in Excel

Excel Functions Used in Age Calculation

This page calculates age in Excel, using the following built-in functions:

Yearfrac-Calculates the number of years between two dates.
Int-Truncates a supplied number down to the closest integer.
Today-Returns the current date.

Age Calculation Using the Excel Yearfrac Function

Entering Dates into Excel Formulas

When entering dates into your Excel age calculation, these should be entered as either:

  • References to cells containing dates
or
  • Dates returned from formulas.

It is not recommended that dates be entered as text representations of dates, as these may be interpreted differently, depending on the date settings on your computer.

The simplest way to calculate age in Excel is to use the Excel Yearfrac Function, which returns the number of years between two dates.

I.e. The number of years between two dates, start_date and end_date is calculated as follows:

=YEARFRAC( start_date, end_date )

The following spreadsheet shows the Excel Yearfrac function, used to calculate the age, on 01/01/2017, of an individual who was born on 03/03/1960:

 AB
1Birth Date:03/03/1960
2Current Date:01/01/2017
3Age:=YEARFRAC( B1, B2 )

The function in cell B3 of the above spreadsheet returns the value 56.82777778.

I.e. on the date 01/01/2017, a person who was born on 03/03/1960, was 56 years old.


How to Return an Integer From the Excel Age Calculation

If you want to tidy up the above age calculation, so that it returns an integer, rather than a decimal value, this can easily be done using the Excel Int Function, which truncates a supplied number down to the closest integer.

In the spreadsheet below, the Int function is used to truncate the result of the Yearfrac function to an integer.

 AB
1Birth Date:03/03/1960
2Current Date:01/01/2017
3Age:=INT( YEARFRAC( B1, B2 ) )

The function in cell B3 of the above spreadsheet now returns the value 56.


Use of the Today Function to Automatically Update an Age Calculation in Excel

If you want your age formula to calculate the age of an individual on the current date, and to update automatically each day, this can be done using the Excel Today function, which returns the current date.

Therefore, if you replace the value in cell B2 of the above spreadsheet, with the formula =TODAY(), this will be used in the age formula in cell B3.

Alternatively, you could use the Today function directly in the age formula, so that the Excel age calculation formula becomes:

=INT( YEARFRAC( B1, TODAY() ) )