Calculating an Age On a Given Date

Alan is president of the local Little League baseball team, and he needs to know the ages of each child on May 1 of each year. He wonders if there is a formula that will return the age on that day.

There are actually a couple of ways you can approach the task. Assuming that the child's birth date is in cell A1, you could use the following formula in most instances:

=(DATE(YEAR(NOW()),5,1)-A1)/365.25

This formula calculates the date serial number (used by Excel internally) for May 1 in the current year. It then subtracts the birth date in A1 from that serial number. This results in the number of days between the two dates. This is then divided by 365.25, an approximate number of days in each year.

For most birth dates, this formula will work fine. If you want something more precise (the imprecision is introduced by the way in which leap days occur), then you can rely on the DATEDIF function in your formula:

=DATEDIF(A1,"5/1/" & YEAR(NOW()),"y")

This returns the age of the person as of May 1 of the current year. If you want even more detail in the results, try this formula:

=DATEDIF(A1,"5/1/" & YEAR(TODAY()),"y") & " years, "
& DATEDIF(A1,"5/1/" & YEAR(TODAY()),"ym") & " months, "
& DATEDIF(A1,"5/1/" & YEAR(TODAY()),"md") & " days"