+ Reply to Thread
Results 1 to 3 of 3

Calculate UK school year band based on student d.o.b

  1. #1
    Registered User
    Join Date
    10-20-2012
    Location
    Petersfield, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Calculate UK school year band based on student d.o.b

    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.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,758

    Re: Calculate UK school year band based on student d.o.b

    Hello M4rk,

    I would put the current "determination date" - i.e. 31/08/2014 in a cell and then use that cell in your formulas - that way you can change that date for subsequent years without having to alter any formulas.

    Let's say that date is in H2 in the MasterDB worksheet and that your table in Years worksheet is in A1:B17 then use this formula in E2

    =IF(D2="","",INDEX(Years!A$2:A$17,MATCH(DATEDIF(D2,H$2,"y"),Years!B$2:B$17,0)))
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-20-2012
    Location
    Petersfield, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Calculate UK school year band based on student d.o.b

    Thanks daddylonglegs, your formula works a treat.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to calculate the school grade of a child based on their birthday
    By ADMJACKS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2016, 07:22 PM
  2. Excel VBA Programm for School Student Database Management
    By betapsp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2014, 03:18 AM
  3. Calculate student class level based on date and graduation year.
    By JoeCrozier in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2014, 02:15 PM
  4. Calculate school grade based on date of birth
    By RegionV in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 10-13-2013, 11:05 AM
  5. Calculating school year from DOB
    By dave the dunce in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-04-2011, 11:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1