ExcelFunctions.net

Search Site:

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 |

Entering Dates into Excel Formulas

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.

The simplest way to calculate age in Excel is to subtracting the current date from the birth date and divide 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 use the following, more complex formula.

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.

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