Auto selected based on DOB

[admiralg]

Hi There,
I’m trying to write a formula (badly) to automatically show me what Scouts group, kids should be in based on their date of birth.

Here’s the tricky part…

So kids start in Beavers aged 6, if their birthday is before 1^st of Sep. If they are born after this date, they must wait a year to join. This follows through as the kids move through the different groups.

I’d like to put in a date of birth, then in the cell beside it, show the group they should currently be in based on their DOB. I'd like to use this formula for moving forward, so I guess this should reference todays date somewhere??

Group	Year Born	Age
Beavers 1	2013	6
Beavers 2	2012	7
Beavers 3	2011	8
Cubs 1	2010	9
Cubs 2	2009	10
Cubs 3	2008	11
Scouts 1	2007	12
Scouts 2	2006	13
Scouts 3	2005	14
Ventures 1	2004	15
Ventures 2	2003	16
Ventures 3	2002	17
Rovers 1	2001	18
Rovers 2	2000	19
Rovers 3	1999	20

<tbody>
</tbody>

Any help is appreciated.

 

[Aryatect]

Hi, Below is 1 option:

Book1
	A	B	C	D	E	F
1	Group	Year Born	Age		DOB
2	Beavers 1	2013	6		10/1/2011	Beavers 2
3	Beavers 2	2012	7
4	Beavers 3	2011	8
5	Cubs 1	2010	9
6	Cubs 2	2009	10
7	Cubs 3	2008	11
8	Scouts 1	2007	12
9	Scouts 2	2006	13
10	Scouts 3	2005	14
11	Ventures 1	2004	15
12	Ventures 2	2003	16
13	Ventures 3	2002	17
14	Rovers 1	2001	18
15	Rovers 2	2000	19
16	Rovers 3	1999	20
Sheet3

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX($A$2:$A$16,MATCH(IF(DATE(YEAR(E2),MONTH(E2),1)>DATE(YEAR(E2),9,1),ROUNDDOWN((TODAY()-E2)/365,0),ROUNDDOWN((TODAY()-E2)/365,0)+1),$C$2:$C$16,0)),"Wait for next year")

 

[admiralg]

Hi Aryatech,
Thank you for your help on this so far. I've tried applying your formula. Its nearly there but doesn't seem to get the change in section if after September the 1st is picked.
Below is an example of what I am trying to explain.
If a kid is looking to join and was born before the end of August in 2013, they would be old enough to join in Beavers first year (Beavers 1). If however their DOB was after this date, then they would have to wait until the following year to join.
This pattern follows as the kids move through the different sections.

Really hoping you can help me out here.

Child	DOB	Section (your formula)	Formula applied correctly should list
A	31 August 2015	Wait for next year	Wait for next year
B	01 September 2015	Wait for next year	Wait for next year
C	31 August 2014	Wait for next year	Wait for next year
D	01 September 2014	Wait for next year	Wait for next year
E	31 August 2013	Beavers 1	Beavers 1
F	01 September 2013	Beavers 1	Wait for next year
G	31 August 2012	Beavers 2	Beavers 2
H	01 September 2012	Beavers 2	Beavers 1
I	31 August 2011	Beavers 3	Beavers 3
J	01 September 2011	Beavers 3	Beavers 2
K	31 August 2010	Cubs 1	Cubs 1
L	01 September 2010	Cubs 1	Beavers 3
M	31 August 2009	Cubs 2	Cubs 2
N	01 September 2009	Cubs 2	Cubs 1
O	31 August 2008	Cubs 3	Cubs 3
P	01 September 2008	Cubs 3	Cubs 2
Q	31 August 2007	Scouts 1	Scouts 1
R	01 September 2007	Scouts 1	Cubs 3
S	31 August 2006	Scouts 2	Scouts 2
T	01 September 2006	Scouts 2	Scouts 1
U	31 August 2005	Scouts 3	Scouts 3
V	01 September 2005	Scouts 3	Scouts 2
W	31 August 2004	Ventures 1	Ventures 1
X	01 September 2004	Ventures 1	Scouts 3
Y	31 August 2003	Ventures 2	Ventures 2
Z	01 September 2003	Ventures 2	Ventures 1
AA	31 August 2002	Ventures 3	Ventures 3
BB	01 September 2002	Ventures 3	Ventures 2
CC	31 August 2001	Rovers 1	Rovers 1
DD	01 September 2001	Rovers 1	Ventures 3
EE	31 August 2000	Rovers 2	Rovers 2
FF	01 September 2000	Rovers 2	Rovers 1
GG	31 August 1999	Wait for next year	Rovers 3
HH	01 September 1999	Rovers 3	Rovers 2

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Aryatect]

Changed a formula a little different, see if this works:

=IFERROR(INDEX($A$2:$A$16,MATCH(IF(DATE(YEAR(E2),MONTH(E2),1) >= DATE(YEAR(E2),9,1),ROUNDDOWN((TODAY()-E2)/365,0),ROUNDDOWN((TODAY()-E2)/365,0)+1),$C$2:$C$16,0)),"Wait for next year")