I would like to be able to calculate the age of a child on a daily basis. The formula you gave me on the course I attended was =TODAY( )-DOB)/365.4 I can't seem to make this work, could you please give me the correct formula. Thank you.

I need this formula so I can know how many children I have under 2 or over 2 on a daily basis.

Hi Ruth,,

I'd go about that in a different way.

First of all, in the column you want the formula to be in, format the column as a number. To do this, highlight the whole column then select format > cells... then choose number and put the decimals figure from 2 to 0.

Then try typing this in the top cell of the row you want:

=IF(today()-C1<=730,1,"")

where C1 is the child's date of birth.

it's saying that if the child's DOB is less than or equal to 730 (the number of days in 2 years), display 1, otherwise display nothing. Then you can count the number of 1's by typing in another cell =sum(D:D) for instance if your formula is in column D

Thank you very much for getting back to me. I still however cannot get the formula to work. All it seems to do it put the date into a number.
How can I take the child's date of birth away from today's date and have this updated automatically daily?

Thank you again for your help.

Can you please tell me what format the child's date of birth is in?

is it 01/10/1999 or anything else?

we'll get there, it's just a small matter of tinkering with the formula

Paul

you can email me with a copy of the spreadsheet detailing what cells/columns you want the results to be in etc..

tealeaves18 AT hotmail.com

I think I might have worked something out. I have changed all the dates of birth to numbers like you suggested but have kept two decimal, in the hope that this give me the year and month. Then I have done the following formula:
=SUM(TODAY()-DOB)/365.4
The date I have been using as an example is 17/10/05. I would like the formula to work so that I would see 1.11 (one year 11 months old), however, with the above formula is comes out as 1.9?

Thanks again.

To be honest Ruth I don't think that's the way forward. I'll have a think and a play around with it

Sorry

The second part to your question Excel cannot do the calculation you need to convert .9 into 11 months.

For that you would need to create a function in excel using VBA that would do this for you as Excel doesn't have one at the moment.

Regards

Carlos

Hi Ruth

In the formula above you missed out a bracket. It should look like this:

=(TODAY()-DOB)/365.4

You should also format the cells to display Number with 1 decimal place

NB If you format to 0 decimals any ages over half (eg 6.5) will be rounded up.

Carlos