Hi, I need to calculate the difference between children's actual age and
their tested reading age.

I have managed to set up a spreadsheet that will work out their age based on
Date of Birth, expressed as custom yy"y" mm"m" e.g. 07y 08m

I now need to be able to type in their tested reading age (e.g. 08y 03m) in
the same format, how do I do this?

And then I need to calculate the difference, (rounded to the nearest 6
months).

Please can someone help me! All the teachers I know will be eternally
grateful!

You might be able to use the DATEDIF function.

This is an old function from 2000 that still works but in not on the help
files in 2002 or 2003. I have not used it for a while but have a look at
http://www.cpearson.com/excel/datedif.htm. for more information.

--
John
MOS Master Instructor
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Hilary from New Zealand" wrote:

Hi, I need to calculate the difference between children's actual age and
their tested reading age.

I have managed to set up a spreadsheet that will work out their age based on
Date of Birth, expressed as custom yy"y" mm"m" e.g. 07y 08m

I now need to be able to type in their tested reading age (e.g. 08y 03m) in
the same format, how do I do this?

And then I need to calculate the difference, (rounded to the nearest 6
months).

Please can someone help me! All the teachers I know will be eternally
grateful!

Hi, thanks for the suggestion.

I have already tried using the DateDiff function, but couldn't get it to
work for me. I think it will also have a problem as it expects the result
always to be positive, and just shows an error if it is negative. I will
have results both positive and negative....

ie. sometimes children have a reading age below their actual age, sometimes
above it...! Results I get when working it out manually range from -60 - +60
months.

I guess it might be possible to use DateDiff embedded in an IF statement to
overcome this? I still can't work out how I can even enter the Reading Age
into a cell though...

Seriously bamboozled! :O) Hilary

Hilary,

I have managed to set up a spreadsheet that will work out their age based
on
Date of Birth, expressed as custom yy"y" mm"m" e.g. 07y 08m

I don't know how you calculated the age as above but I used DATEDIF()

What I did was:

A2: Pupil's Date of Birth (labelled in A1 as "DOB")
B2: =DATEDIF(A2,TODAY(),"y")
C2: =DATEDIF(A2,TODAY(),"ym")
The two cells B2 & C2 were labelled in B1 & C1 as "Years" & "Months"
respectively

A3: Labelled as "Tested Reading Age"
B3 & C3 were then used to enter the Tested Reading Age in years & months
respectively

B4: =DATEDIF(MIN(A2,A4),MAX(A2,A4),"y")
C4: =ROUND((DATEDIF(MIN(A2,A4),MAX(A2,A4),"ym")+1)/6,0)*6
A4: =DATE(YEAR(TODAY())-B3,MONTH(TODAY())-C3,DAY(A2)-1)

I then hid Row 4 (select any cell in row then Format Row Hide), but this
is not necessary if you don't want to.

A6: =IF(SUM(B4:C4)=0,"Reading age correct",IF(A2A4,"Reading age ahead by "
&YEAR(DATE(B4,C4+1,1))-1900&"Year"&IF(YEAR(DATE(B4,C4+1,1))-1900<1,"s","")&
" and "&IF(C4=0,0,IF(MONTH(DATE(B4,C4,1))=12,0,MONTH(DAT E(B4,C4,1))))&
" Months","Reading age behind by " &YEAR(DATE(B4,C4+1,1))-1900&"Year"&
IF(YEAR(DATE(B4,C4+1,1))-1900<1,"s","")&" and "&
IF(C4=0,0,IF(MONTH(DATE(B4,C4,1))=12,0,MONTH(DATE( B4,C4,1))))&
" Months"))

Now when you enter the tested reading age years in B3 & the odd remaining
months in C3 I got the answer that I think you are looking for.

E-mail me direct if you want an example spreadsheet.

--
HTH

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


with @tiscali.co.uk


"Hilary from New Zealand" <Hilary from New
wrote in message
...
Hi, I need to calculate the difference between children's actual age and
their tested reading age.

I have managed to set up a spreadsheet that will work out their age based
on
Date of Birth, expressed as custom yy"y" mm"m" e.g. 07y 08m

I now need to be able to type in their tested reading age (e.g. 08y 03m)
in
the same format, how do I do this?

And then I need to calculate the difference, (rounded to the nearest 6
months).

Please can someone help me! All the teachers I know will be eternally
grateful!