Age Calculation in Excel

Dates in Excel

If you want to create a formula for age calculation in Excel, it is important that you understand that dates are simply stored as integers in Excel.

For example, in most computer systems, Excel stores the date 01-Jan-1900 as the number 1, the date 02-Jan-1900 as the number 2, etc.

Therefore, you can subtract dates in the same way that you can subtract any other numbers in Excel. This is seen in the Excel age calculations described below.


Index:
Simplified Age Calculation in Excel
Accurate Age Calculation in Excel
Automatic Updates Using the Today Function

Simplified Age Calculation in Excel

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 or as date serial numbers for the following reasons:

- Text date values can be interpreted differently, depending on the date system and date interpretation settings on your computer
- Date serial numbering varies across different computer systems

Many people calculate age in Excel by simply subtracting the current date from the birth date and dividing this result by 365.25 (the average number of days in a year). This resulting value is then truncated to an integer using the Excel Int function. This is shown below:

  A B
1 Birth Date: 21-Nov-1960
2 Current Date: 01-Jan-2011
3 Age: =INT( (B2-B1) / 365.25 )

While the above formula works in over 99% of cases, inaccuracies are introduced, due to the 'average' day count of 365.25. For example, if the birth date is 01-Mar-2000 and the current date is 01-Mar-2010, the above formula gives the incorrect age 9, when the correct age is 10.

Similar inaccuracies can also occur if you use the most frequent day count of 365. Therefore, if you want 100% accuracy in you Excel age calculation, you need to read on ...


Accurate Age Calculation in Excel

Although the following formula may appear to be complicated, this level of complexity is necessary if you want to accurately calculate age in Excel. The formula uses the Excel date functions, along with a nested Excel If function:

  A B
1 Birth Date: 21-Nov-1960
2 Current Date: 01-Jan-2011
3 Age: =YEAR(B2) - YEAR(B1) - IF( MONTH(B2) > MONTH(B1), 0,
IF( MONTH(B2) < MONTH(B1), 1,
IF( DAY(B2) < DAY(B1), 1, 0 ) ) )

The above formula initially calculates the number of years between the current date and the birth date. However, the resulting value needs to be adjusted, depending on whether the birthday has passed during the current year. To do this, we have used the Excel If function to first check if the current month is less than or greater than the month of the birth date. If the month is the same, the days of the month are compared to determine whether the birthday has been passed during the current year.


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

If you want your age formula to update automatically, depending on the current date, this can be done using the Excel Today function. This function simply returns the current date. Therefore, if you replace the value in cell B2 of the above spreadsheet, with the formula =TODAY(), the age formula in cell B3 will update automatically with the current date.

Alternatively, you could replace every reference to cell B2 with the Today function, so that the Excel age calculation formula becomes:

=YEAR( TODAY() ) - YEAR(B1) - IF( MONTH( TODAY() ) > MONTH(B1), 0,
IF( MONTH( TODAY() ) < MONTH(B1), 1,
IF( DAY( TODAY() ) < DAY(B1), 1, 0 ) ) )
Return to the Excel Formulas Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2015 ExcelFunctions.net