Age Formula

[Guest]

hi all,

If I am using the formula below to calculate the age when entering the date
of birth in column A:A

=DATEDIF(A:A,TODAY(),"y") & "Y " & DATEDIF(A:A,TODAY(),"ym") & "M " &
DATEDIF(A:A, TODAY(), "md") & "D"

The age then appears as in: 9Y 3M 15D

I need to write 2 formulas in the end of that age column. One to count the
number of children who are under 3Y 0M 0D, and one for children who are
between 3Y 0M 0D and 10Y 0M 0D (inclusive).

Any ideas?

cheers,
Tendresse

 

[JE McGimpsey]

One way:

J1: =COUNTIF(A:A,">" & DATE(YEAR(TODAY())-3, MONTH(TODAY()),
DAY(TODAY())))

J2: =COUNTIF(A:A,">=" & DATE(YEAR(TODAY())-10, MONTH(TODAY()),
DAY(TODAY()))) - J1

 

[JE McGimpsey]

Just a note on your technique:

DateDif assumes that months, using the "md" switch, are as long as the
starting month, so that if the value of the cell in column A is, say

A1: 31 Jan 2005

then on 1 March 2008, the result will be

3Y 1M -1D

and on 2 March 2008, the result will be

3Y 1M 0D

which may not be what you're expecting...

 

[Guest]

ummm .. so how do I go around that?

thanks a million for your reply ..

 

[JE McGimpsey]

Hard to say (which is why I mentioned it to note rather than as an
error). Because months are rather fuzzy concepts, it depends on what you
want.

For example:

Assume the birthday is 31 January 2006.

Does 28 February 2008 result in 2Y 1M 0D, or 2Y 0M 28D?

If it's the former, is 29 February 2008 *also* 2Y 1M 0D?