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??


[TABLE="width: 274"]
<tbody>[TR]
[TD]Group
[/TD]
[TD]Year Born
[/TD]
[TD]Age
[/TD]
[/TR]
[TR]
[TD]Beavers 1
[/TD]
[TD]2013
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Beavers 2
[/TD]
[TD]2012
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Beavers 3
[/TD]
[TD]2011
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Cubs 1
[/TD]
[TD]2010
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Cubs 2
[/TD]
[TD]2009
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Cubs 3
[/TD]
[TD]2008
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]Scouts 1
[/TD]
[TD]2007
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Scouts 2
[/TD]
[TD]2006
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]Scouts 3
[/TD]
[TD]2005
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]Ventures 1
[/TD]
[TD]2004
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]Ventures 2
[/TD]
[TD]2003
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]Ventures 3
[/TD]
[TD]2002
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]Rovers 1
[/TD]
[TD]2001
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]Rovers 2
[/TD]
[TD]2000
[/TD]
[TD]19
[/TD]
[/TR]
[TR]
[TD]Rovers 3
[/TD]
[TD]1999
[/TD]
[TD]20
[/TD]
[/TR]
</tbody>[/TABLE]

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.


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

 

[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")