Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel training seminar - Calculated age of person each day
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
excel training seminar - Calculated age of person each day
Resolved · Low Priority · Version Standard
Reply Thu 27 Sep 2007, 16:41Delegate Ruth said...
Ruth has attended:
Excel Intermediate course
Powerpoint Intermediate Advanced course
Powerpoint Introduction course
Excel Advanced course
Calculated age of person each day
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.
For upcoming training course dates see: Pricing & availability
Reply Thu 27 Sep 2007, 17:07Delegate Paul said...
RE: calculated age of person each day
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
Reply Fri 28 Sep 2007, 09:17Delegate Ruth said...
RE: calculated age of person each day
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.
Reply Fri 28 Sep 2007, 12:39Delegate Paul said...
RE: calculated age of person each day
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
Reply Fri 28 Sep 2007, 12:46Delegate Paul said...
RE: calculated age of person each day
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
Reply Fri 28 Sep 2007, 12:49Delegate Ruth said...
RE: calculated age of person each day
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.
Reply Fri 28 Sep 2007, 13:58Delegate Paul said...
RE: calculated age of person each day
To be honest Ruth I don't think that's the way forward. I'll have a think and a play around with it
RE: calculated age of person each day
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
RE: calculated age of person each day
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
|