Calculating school year from DOB

[dave the dunce]

I help out at a kids rugby club and am using Excel to record the kids details. We need to know which year they are in at school and I 'm trying to make the Excel 2003 spreadsheet work this out. I have one column calculating their age on current day but can't develop a formula that calculates school year because that runs from 1/Sept/xx to 31/Aug/xx. eg my Son is in year 1 DOB 25/03/02. I am trying to make the s/s future proof in that it can be used for future years without alteration. Any ideas? Anybody?

[ratcat]

Welcome,


I would set up a database of information (on another spreadsheet) containing the school grade to the general age.

eg. If age 15 (col A) it should mean that the kid is in year 09 (col B).
eg. If age 16 (col A) it should mean that the kid is in year 10 (col B).

On the original speadsheet

eg. Col D contains the ages then Col E will contain the vlookup formula

Please Login or Register  to view this content.

I've added the IF statement to return the cell blank.

Well that one idea.


Edit: I though a little bit longer and it only fixed half the problem. I have an idea I'm going to try

[ratcat]

Well I found a common change in the number throughout the school ages and years.

What happens in thoery does not happen in real life.

This is as far I've gotten.

Have a play with the DOB on the spreadsheet and see what you think.

Welcome to PM me for further information

[Richard Buttrey]

Well I found a common change in the number throughout the school ages and years.

What happens in thoery does not happen in real life.

This is as far I've gotten.

Have a play with the DOB on the spreadsheet and see what you think.

Welcome to PM me for further information

Hi,

Obviously the VLOOKUP will work fine. You could also use the formula:

Please Login or Register  to view this content.

Rgds

[daddylonglegs]

="Year "&YEAR($B$1)-YEAR(C5)-5+IF(DATE(YEAR(C5),8,31)>C5,1,0)

But the only updating variable in this formula is YEAR($B$1). Of course that will change on 1st January which means that all years will also increase by 1 on that date.

Dave says that his son's DOB is 25/03/02 which places him in year 1. Assuming he is deemed to be in Year 1 right up until 31st August 2008....and in Year 2 from the next day then you can get the year like this

="Year "&MAX(0,DATEDIF(DATE(YEAR(C5+122),9,1),B$1,"Y")-4)

where C5 contains DOB and B1 today's date

"Year 0" = pre-school

[ratcat]

...................

="Year "&MAX(0,DATEDIF(DATE(YEAR(C5+122),9,1),B$1,"Y")-4)

where C5 contains DOB and B1 today's date

"Year 0" = pre-school

Very Cool.

This answer is ageing like a bottle of wine. lol. Gets better and better.

Well back to studying the formula. One half I understand and the other like what the...... how does that work ??

And Richard Buttrey formula too.

[dave the dunce]

Thanks for all the input guys. Its a problem that I've been trying to get my head around for about ten days. I'll have a play with what you lot have given me so far and let you know how I've got on.

[dave the dunce]

Daddylonglegs has cracked it. Thank you very much. I have just refined the formula with an IF statement at front so that cell reports clear if no DOB has been entered. i.e:
=IF(B2="","","Year "&MAX(0,DATEDIF(DATE(YEAR(B2+122),9,1),NOW(),"Y" )-4))
Where B2 = DOB

I have also been able to do away with the reference to the cell with todays date by replacing it with 'NOW()' in formula which is of course today's date.

As Ratcat says this one started out as a cheeky little Beajolais Nouveau and is maturing into a fine Bordeaux. That reminds me I must go and pull the cork for Sunday Dinner's accompaniment.

[alexmcdonald]

I'm confused: trying to calculate school years from a date of birth. In my case I need to include pre-school children as well as some special school children (young people) who may carry on beyond age 18. Can anyone help please?

[alexmcdonald]

Thanks to Dave the Dunce, just found your formula (not before asking a dum question) and it works. Brilliant