Calculate Age

[Guest]

I have a column (E:E) for Dates of Birth, and the next column (F:F) has a
formula to calculate the age as follows: =(TODAY()-E:E)/365
Cells of column F:F are formatted as "Number" with decimal places "1".
I also have a conditional formatting to highlight cells in that column that
have an age between 0 and 3. the condition reads as follows:
Cell value is BETWEEN 0 AND 3

I also have a cell at the bottom of column F:F that counts the TOTAL number
of children under 3 years of age using the formula: =COUNTIF(F12:F18, "<3")

So far all sound logical. However, something very weird is happening:

Considering TODAY() is 23/05/07, when I enter a date of birth 02/06/04, the
age then appears as 3.0, this age cell gets highlighted accroding to the
Conditional Formatting, and the child gets counted in the TOTAL even though
the formula in the TOTAL cell counts those <3 not those <=3

And if I enter a date of birth 15/05/04, the age cell calculates the age as
3.0, however, the cell doesn't get highlighted according to the Conditional
Formatting and that child doesn't get counted in the TOTAL cell!

How come the values of the age cells are treated differently even though
they are both equal 3.0?

Your assistance will be greatly appreciated.
Tendresse

 

[Guest]

Tendresse

I think the issue you are having is the fact that even though you limit the
cell value to 1 decimel point, the calculations use that actual numbers. If
you extend the values of column F, the first one is 2.969 and the second one
is 3.019. Therefore the first one would get counted and the second one would
not.

Hope this helps.

 

[Guest]

I should have also said that you can go to
tools---options--calculation---check Precision as displayed box.

 

[Guest]

Thank you very much, Gerigto. That makes sense. Well, I suppose the way to go
around it is to format the age cell to 2 decimal points rather than 1.

Thanks again for your reply. That was really helpful.

Tendresse

 

[Guest]

Gerigto,

That last tip was very handy. Thanks a million.

Tendresse

 

[Sandy Mann]

If it is not too late, use the DATEDIF() function which is only documented
in XL2000 but is in every version since XL95:

=IF(E:E,DATEDIF(E:E,TODAY(),"y"),"")

This will return a the age with no decimal places.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk