By Allen Wyatt for Excel.Tips.Net
Calculating Ages in Years and Months for Microsoft Excel
Many times in early childhood education, a child’s age in Years.Months format is needed for reporting, admitting, and evaluation. Basically, this format shows the number of elapsed years and months since birth, separated by a period.
There are several ways you can go about getting the desired age. The formula you choose to do the work depends, quite literally, on your preference in formulas.
Assuming that you have the birth date in cell A1, you might think you could use some math on the date to determine the proper information. For instance, you might think that you could use this formula:
=NOW() – A1
This produces a value that is the difference between the two dates, but if you then apply a custom format (“yy.mm”) to the result, you’ll find out that it doesn’t give the sought-after Years.Months. You get something close; the years are right, but the months vary from 1 to 12 when they should vary from 0 to 11. (Somebody can be 12 years and 0 months old, but it is not proper to say 12 years and 12 months old.)
This means that you need to start casting about for a formulaic approach. A simple formula would be the following:
=YEAR(NOW())-YEAR(A1) & “.” & MONTH(NOW())-MONTH(A1)
