SOLVED

Calculate school cohort

New Contributor

Newbie here so please be gentle! I am not a mathematician and am self taught around formula

 

I need to know the school cohort (year that a child is starting in reception) I deal with the admin for a pre school/nursery.

 

I am using Windows 8.1 with Office 365 and 64 bit

 

I have a spreadsheet which has the name of a child with their date of birth (and a whole heap of other information as well but that is by the by)

 

I want to find out the cohort they are in eg:

 

If the date of birth falls between 01/09/2018 and 31/08/2019 the cohort year is 2023
      
and If the date of birth falls between 01/09/2019 and 31/08/2020 the cohort year is 2024
      
and if the date of birth falls between 01/09/2020 and 31/08/2021 the cohort year is 2025

 and so on.....

 

I have looked at a number of different options but just can't seem to find the correct formula.

 

Can you help me please?

 

2 Replies
best response confirmed by Lucy_Walker (New Contributor)
Solution

@Lucy_Walker Here are 2 versions that will work:

=YEAR(C2)+4+(MONTH(C2)>8)
=YEAR(C2)+4+(C2>DATE(YEAR(C2),8,31))

The 1st is slightly more simple and adds 4 to the DOB year and then will add 1 if the month of the DOB is > 8 (after August) and adds 0 if not

The 2nd starts the same but then compares the DOB to a specific DATE made by the YEAR from the DOB and then month 8 and day 31.  This second version would be needed if the break point was in the middle of the month.

Hope that help solve your problem and maybe gives you a little insight into a couple excel formulas.

@mtarler

Thank you for firstly being so speedy and secondly for that wonderful formula which is now residing in my spreadsheet!! Allowing me to remove 4 columns and a huge headache!!

Have a great day

Lucyx