excel formula

[Russellgram]

Hello Excelers,

I am trying to figure out how solve the following problem: I have to keep track of boys' ages as they change every month and have the information at my finger tips. I set up a spread sheet as below, from January to December of that year.

DOB = date of birth and 15.08 means 15 years & 8 months old - 1/1/02=the date of the month

Column Column Column etc. etc.
A B C D E
Name DOB 1/1/02 2/1/02 3/1/02
Jones, R. 3/28/86 15.10 15.11 15.12
(For some reason, when my message is posted all the information right above is crunched together to on the left side and may not make sense to anyone)

My first problem is that I want 15.12 to turn to 16.00, not 15.12 and continue 16.01, 16.02 and so on.

I have been using the following formula
=C2+0.01, =C2+0.02, etc. This works fine until it comes to converting the 15.11 into 16.00. It just changes into 15.12 and I also can't figue out how to go on to the next January.

The second problem is not knowing what to do when I get to December of the year. How do I get back or go on to January of the next year without having to reenter all the data for 200 plus boys? The list will only continual to grow.

A gentleman suggested that I try the following formula:

=if(MOD(C2,1)=.11,C2+.89,c2+.01)

When I tried it the excel program told me it was not correct and did I want them to correct the formula. I clicked yes and it changed the formula to:

=IF(MOD(C2,1)=0.11,C2+K4.89,C2+0.01

When I copied it in the next cell it changed the formula according to the column I was in, but when it got to the 15.12 column it did not change it into 16.00.

First, I would like to know what this formula is saying, and Secondly, I don't know why it is not working.

Thanks in advance for any guidance anyone can give me!

Rich










[ This Message was edited by: Russellgram on 2002-03-09 20:59 ]

[ This Message was edited by: Russellgram on 2002-03-09 21:04 ]

[nisht]

friend,

download file..
"agetime"

i have use the function datedif provided by exel..

http://www.pexcel.comdownload.htm

[Dave Hawley]

Hi
Russellgram

I must admit I get a bit lost trying to read you explanation, but if you want a persons age in Years and months you would most likely be better to have the years in one column and the months in another. This would make for a better spreadsheet! Try this:

Put the formula =TODAY() in say cell A1 this will be used to reference todays date in ALL your formulas. The reason why we dont nest the function into the others is because TODAY is a volatile function and using it to liberally will cause re-calculation slow dowm.

No in the Column for the person age in years simply use:
=DATEDIF($A$2,$A$1,"y")

Now in the next Column use:

=IF(MONTH($A$1)
This will calculate the months.

In both formulas the assumption is that there is a bith-date in cell A2




_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
http://www.microsoftexceltraining.com

[ This Message was edited by: Dave Hawley on 2002-03-09 23:22 ]

[Dave Patton]

Try Datedif
Datedif is in Excel but it is only documented in Excel 2000.

in one cell =DATEDIF(A77,TODAY(),"y")
in another =DATEDIF(A77,TODAY(),"ym")

or with named ranges
=DATEDIF(DOB,cMonth,"y")
=DATEDIF(DOB,cMonth,"ym")

in one cell

=DATEDIF(DOB,cMonth,"y")& "y "&DATEDIF(DOB,cMonth,"ym")&"m"

You could also look at =YEARFRAC(DOB,cMonth)

revise references as necessary.