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.

 

 

 

Comments

  1. Eddie says:

    Nice post, but the formula for age in years, months and days doesn’t work: If you’re born on 12/24/1975 the formula returns 37 years, -1 months and -8 days…

    • admin says:

      Thank you very much. I have taken that formula out for the time being, until I come out with a more accurate formula.

      • admin says:

        I have now replaced the formula with a new one which I hope will be more accurate.
        Your feedback is appreciated.

  2. Eddie says:

    I like that approach with INT and MOD! It seems to get it right as long as the date in A1 is not a leap year. I guess that could be solved by wrapping an IF-statement, such as =IF(MOD(YEAR(A1),4)=0,… around it.

    I actually found an obscure function which is not even documented by Microsoft, and when you start typing it, it’s not in the list of suggested formulae…

    =DATEDIF(A1,A3,”y”)&” year(s), “&DATEDIF(A1,A3,”ym”)&” month(s) and “&DATEDIF(A1,A3,”md”)&” day(s)”

    It works, but I get a little suspicious by the fact that MS are so secretive about it. Very strange indeed…

    • admin says:

      Thanks again. I will put the leap year issue in consideration.
      As for the DATEIF function, I saw many people use it, but as you said it is not documented. In such cases one should be careful. It may be dropped from a new release or update and then you are stuck.
      Regards.