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.
|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 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 ...
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, 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 ) ) )