Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the difference between ages, show as yy mm
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the difference between ages, show as yy mm
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the difference between ages, show as yy mm
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate the difference between ages, show as yy mm
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate difference between dates and times | Excel Discussion (Misc queries) | |||
Calculate the difference between two times | Excel Worksheet Functions | |||
Calculate the difference two times | Excel Discussion (Misc queries) | |||
Calculate the difference between two times | Excel Worksheet Functions | |||
Calculate difference from assending data | Excel Discussion (Misc queries) |