I use xl as a membership database for the charity I volunteer for. The workbook consists of 2 sheets, 1 called MasterDB and the other called Years.
The MasterDB contains general student details such as name, address, date of birth etc. The Years worksheet contains UK school year bands (N – 15) determined by Age as at 31st Aug (4 – 19).
I need a formula that will return the students year banding as at the 31/08/2014 into column E on the Master DB (the current school year runs between 01/09/2014 to 31/08/2015)
The Years sheet contains:
School Year Lowest Age
N 4
R 5
1 6
2 7
3 8
4 9
5 10
6 11
7 12
8 13
9 14
10 15
11 16
12 17
13 18
14 19
The MasterDB sheet contains: (based on current school year) / age auto updates to current
Forename Surname Age DOB Year
Adam Bee 14 31/08/2000 9
Betty Car 14 01/09/2000 8
David Edwards 6 22/06/2008 1
Filo Gino
Henry Ford 6 18/10/2008 R
As you can see although the 1st 2 students are both now 14 and were born only a day apart Adam is in the higher set than Betty because he was older on the cut-off date.
Bookmarks