If you want to create a formula for age calculation in Excel, it is important that you understand that dates and times are simply stored as numbers in Excel. For example, in most computer systems, the number 1 represents the date 01-Jan-1900, the number 2 represents the date 02-Jan-1900, etc.
As dates and times are simply numerical values in Excel, you can add and subtract dates and times, in the same way as you can perform these mathematical operations on any other numbers in Excel. This is seen in the Excel age calculations described below.
|Simplified Age Calculation in Excel|
|Accurate Age Calculation in Excel|
|Automatic Updates Using the Today Function|
When entering dates into your Excel age calculation, these should be entered as either:
|-||References to cells containing dates|
|-||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:
|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 would also occur if we used the most frequent day count of 365. Therefore, if you want 100% accuracy in you Excel age calculation, you are advised to read on ...
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:
=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.
If you want your age formula to update automatically, with 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 ) ) )