Calculating school year from DOB

**dave the dunce** · 03-28-2008, 03:47 PM

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** · 03-28-2008, 11:55 PM

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


=IF(D2="","",vlookup(D2,Sheet2!A1:B1000,2,false)

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** · 03-29-2008, 06:53 AM

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** · 03-29-2008, 07:26 AM

Originally Posted by 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

Hi,

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

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

Rgds

**daddylonglegs** · 03-29-2008, 11:46 AM

Originally Posted by Richard Buttrey

="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** · 03-29-2008, 05:42 PM

Originally Posted by daddylonglegs

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

="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.

**alexmcdonald** · 04-04-2011, 11:36 AM

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?