Date formula Request

[m_geragh]

Hi all

I am happy to give formula's a go, but I am out of my depth with this one.

In a nutshell I would like a formula that would tell me, if somebody based on their date of birth is statutory school age.

The requirements for statutory school are detailed below. If anybody is up for the challenge it would be greatly appreciated!!

Thanks in advance

---------------------
What does Compulsory school age mean?

The term "Compulsory School Age" is confusing. It does not actually mean that school is compulsory during the qualifying period. It does, however, mean that the parent or guardian of a child of "compulsory school age" has a duty to provide an education (subject to section 7 of the 1996 Education Act) during that period.

When does it start?

Your child is of "compulsory school age" on the 1st January, 1st April or 1st September following their 5th birthday.

Children becoming 5 years old between 1st January and 31st March are of compulsory school age at the beginning of the term after 1st April.

Children becoming 5 years old between 1st April and 31st August are of compulsory school age from the beginning of the term after 1st September.

Children becoming 5 years old between 1st September and 31st December are of compulsory school age from the beginning of the term after 1st January.

When does it end?

In practice a person ceases to be of "compulsory school age" on the last Friday of June during the year that s/he becomes 16 provided that the child's birthday is before the beginning of the next school year.

If his/her 16th birthday is after the start of the new school year, s/he is still of "compulsory school age" until the end of the last Friday in June of the following year. Exceptions cannot be made for any reasons.

 

[Dave Patton]

I am not sure how your data is structured or how you want to show the results.

You may be able to build a solution with part of the information provided below.
Two different approaches are shown.

Excel 2010
	A	B	C	D	E	F	G	H
1			1-Jan-17	1-Apr-17	1-Sep-17	1-Jan-18		Compulsory school Age
2	DOB	Date 5 yrs	31-Dec-16	31-Mar-17	31-Aug-17	31-Dec-17
3	1-Jan-12		4	5	5	5		1-Apr-17
4	1-Dec-12		4	4	4	5		1-Jan-18
5
6	1-Jan-12	1-Jan-17	FALSE	TRUE	FALSE	FALSE		1-Apr-17
7	1-Dec-12	1-Dec-17	FALSE	FALSE	FALSE	TRUE		1-Jan-18
8
4e

Cell Formulas
Range	Formula
C3	=DATEDIF($A$3,C2,"y")
C6	=B6<=$C$2
D3	=DATEDIF($A$3,D2,"y")
D6	=AND($B6>=C$2,$B6<=D$2)
E3	=DATEDIF($A$3,E2,"y")
E6	=AND($B6>D$2,$B6<=E$2)
F3	=DATEDIF($A$3,F2,"y")
F6	=AND($B6>E$2,$B6<=F$2)
H3	=INDEX($C$1:$F$1,,MATCH(5,C3:F3,0))
H6	=INDEX($C$1:$F$1,,MATCH(TRUE,C6:F6,0))
B6	=DATE(YEAR(A6)+5,MONTH(A6),DAY(A6))
B7	=DATE(YEAR(A7)+5,MONTH(A7),DAY(A7))

 

[Dave Patton]

or if you just require the relevant date based on DOB.
Build a lookup table or include the information within the lookup formula.

Excel 2010
	A	B
9	1-Jan-12	1-Apr-17
4e

Cell Formulas
Range	Formula
B9	=LOOKUP(A9,$L$3:$M$20)