How to calculate age in Excel 2010

To calculate a person (or anything else) age you basically subtract his date of birth from today’s date. However which date functions to use depends on the amount of precision and the output format you want.

Suppose that we want to calculate the age of someone who is born on 15/06/1973 and we have his birth date in cell A1.

1. If we want his age in whole number years only, e.g. 40 years, then we can use this formula:

=YEAR(TODAY())-YEAR(A1)

2. If we want his age in whole numbers and a fraction of a year, e.g. 40.5 years, then we can use this formula:

=(TODAY()-A1)/365.25

3. If we want his age in years and months, then we can use a formula like this one:

=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”

The output of this formula will be as follows:

40 years and 2 months.

4. If we want to extend the previous formula to include days, then we can use a complex formula like this one:

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375;1)*30.4375) & ” days”

The output of this formula will be as follows:

40 years, 2 months and 18 days.

This formula could yield a one day difference due to approxmation.

What if you want to calculate the age in a target date other than today’s date?

Well, simply put the target date in another cell and replace the function TODAY() with the reference of the target cell. In the previous example if we want to calculate the age in 31 July 1995, then we can put 31/7/1995 in cell B1 and modify the first formula to be like this:

=YEAR(B1)-YEAR(A1)

Note:

Excel does not recognize dates before year 1900 (windows) or 1904(Macintosh). So formulas given above are also subject to Excel limitations.