Excel.Tips.Net: Ages in Years and Months

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)

Article Continued Here

453 total views, 1 views today

(Visited 298 times, 1 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.