age calculations

[Guest]

How can you calculate the age of a person on a given date (and knowing the
date of birth)? And how can you format cells to show age in years and months
- and calculate the difference between two eg the difference in years and
months between someone who is 10 years and 7 months and someone who is 11
years and 2 months?
And how can this be displayed in a chart?

 

[Gord Dibben]

Tulip

=DATEDIF(A1,B1,"y") & "years," & DATEDIF(A1,B1,"ym") & "months,"&
DATEDIF(A1,B1,"md") & "days,"

Birthdate in A1....................given date in B1

Watch for wordwrap.................above formula is one line.


Gord Dibben MS Excel MVP

 

[Dave O]

The answer depends on how fussy you need / want to be about fractional
months. A person's exact age in terms of years is easy enough; months
are trickier because they are not the same number of days.

Here is a simplified way to do it, with the birthdate in A1 and the
given date in B1. Assume a month is 365 / 12 days. The Years
calculation is
=INT((B1-A1)/(365/12)/12)

The months calculation is
=MOD((B1-A1)/(365/12),12)

If you try this you'll likely get a result with decimal places in the
months response, so you may want to apply a rounding protocol or at
least be prepared to explain what the fractions truly mean.