Calculate the difference between ages, show as yy mm

[Hilary from New Zealand]

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!

 

[john the confused]

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]

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

 

[Sandy Mann]

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(A2>A4,"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(DATE(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

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


"Hilary from New Zealand" <Hilary from New
(e-mail address removed)> wrote in message