Tuesday, February 08, 2005

Working out a persons Age in Excel

Martin Green has written some very precise notes explaining the formula he has used to work out a persons age on any given day.

For all the details visit his website and review his explanation.
I will give you the formula and the explanation of each component.

=IF(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())-YEAR(A1),
IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1)),
YEAR(TODAY())-YEAR(A1),(YEAR(TODAY())-YEAR(A1))-1))

I've written this calculation on three lines for clarity but you should write is as a single expression without spaces. It assumes that cell A1 contains the person's date of birth. Here's what it says...

  • IF(MONTH(TODAY())>MONTH(A1)

    • If this month is later than the month of the persons birthday...

  • YEAR(TODAY())-YEAR(A1)

    • ...subtract the year in which they were born from this year because they must have had their birthday.
    But what if we haven't passed the month in which they were born. We might be in that month, or we might not have reached it yet. Let's find out...

  • IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1))

    • If we are currently in the month of the person's birthday and it is either their birthday today or we have passed it...

  • YEAR(TODAY())-YEAR(A1)

    • ...subtract the year in which they were born from this year because they must have had their birthday.
    But what if this isn't the month in which they were born. We know we haven't passed their birthday so...

  • (YEAR(TODAY())-YEAR(A1))-1

    • ...subtract the year in which they were born from this year then subtract 1, because they haven't had their birthday yet.
    Phew!


    Posted by: Jethro | 6:02 PM |