Calculate Sports Division based on DOB

[jkfromk]

I have the following excel formula that works fine however I am trying to get to work in a SharePoint list:

=IF(ISBLANK(B1),"",MAX(IF(MOD(IF(MONTH(B1)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(B1)),2)>0.5,1,0)+IF(MONTH(B1)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(B1)),6))

Instead of the B1 reference I have a column in the list called DOB.  I would like to calculate the division based their DOB. I tried substituting $DOB for B1 but that did not work. 

If there is a better way to do this other than trying to fit the formula above I'm open to it. Essentially I need to calculate the persons age as of 9/1 of the current year.   And based on that calculated age they would fit in the following divisions:

Age as of 9/1/23

6U - Age <6

8U - Age 7-8

10U - Age 9-10

12U - Age 11-12

14U - Age 13-14

16U - Age 15-16

18U - Age 17-18

Thank you in advance. 

[#2 · Accepted solution]

Hi @jkfromk 

you can create a calculated column to calculate the age in years of the person in that specific date. using a formula like =DATEDIF(Birthday,DATE(YEAR(TODAY()),9,1),"Y") > You may need to adjust the month/day order depending on your regional settings.

And then create a new one where you add your conditions to categorize depending on the age using If conditions =IF([AGE-Y]<6,"U6",IF([AGE-Y]<8,"U8","O8")). You need to upate the formula and add the remaining conditions to all scenarios.

In the view will present the data:

Regards

[#3]

@luis-ribeiro thank you! That worked. I ended up using this:

=IF([AGE 9/1]<6,"6U",IF([AGE 9/1]<8,"8U",IF([AGE 9/1]<10,"10U",IF([AGE 9/1]<12,"12U",IF([AGE 9/1]<14,"14U",IF([AGE 9/1]<16,"16U"))))))