Forum Discussion
Calculate Sports Division based on DOB
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.
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
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
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"))))))
