1

I am trying to build a Training Plan that tracks whether or not a person is on track to finish within a set time period.

I built 1 calculated column "Calc - Days in Training" to show if under 182 =0, greater than 182 and less than 273 =1, greater than 274 and less than 366 =2, 365 =3.

=IF([Days in Training]<182,0,IF(AND([Days in Training]>181,[Days in Training]<273),1,IF(AND([Days in Training]>274,[Days in Training]<366),2)))

I built 1 calculated column "Calc - Completion for On Track?" to show (from another calculated column "Completion") 100% =3, greater than 74% and less than 100% =2, greater than 49% and less than 75% 1, less than 50% =0.

=IF([Completion]=100%,3,IF(AND([Completion]>74%,[Completion]<100%),2,IF(AND([Completion]>49%,[Completion]<75%),1,IF([Completion]<50%,0))))

Both of these columns generate the values I intended. I would like the new column "On Track?" to show YES under certain conditions (Less than 182 regardless of "Completion" value, Between 182 \ 273 and Greater than 50%, Between 274 \ 366 and Greater than 75%) and alternatively NO under certain conditions (Between 182 \ 273 and Less than 50%, Between 274 \ 366 and Less than 75%, 365 and Less than 100%).

I tried the following formula for the new column "On Track?"

=IF(AND([Calc - Days in Training]="0",[Calc - Completion for On Track?]="0"),"YES",IF(AND([Calc - Days in Training]="0",[Calc - Completion for On Track?]="1"),"YES",IF(AND([Calc - Days in Training]="0",[Calc - Completion for On Track?]="2"),"YES",IF(AND([Calc - Days in Training]="1",[Calc - Completion for On Track?]="1"),"YES",IF(AND([Calc - Days in Training]="1",[Calc - Completion for On Track?]="2"),"YES",IF(AND([Calc - Days in Training]="1",[Calc - Completion for On Track?]="0"),"NO",IF(AND([Calc - Days in Training]="2",[Calc - Completion for On Track?]="2"),"YES",IF(AND([Calc - Days in Training]="2",[Calc - Completion for On Track?]=”1”),"NO",IF(AND([Calc - Days in Training]="2",[Calc - Completion for On Track?]=”0”),"NO")))))))))

but this does not return any value.

Improve this question
5
  • Can you mention all column names & their types used in 2 calculated columns you have already created? Also, add the formulas you used for those columns to your original question by editing it. Screenshot of list view with sample data may help to understand the data structure & your requirements. Commented Feb 1, 2024 at 8:52
  • @GaneshSanap-MVP I have added the column names and formulas to the original question. Commented Feb 1, 2024 at 14:43
  • Set return data type of first 2 calculated columns to "Number" from calculated column settings and then try removing double quotes ("") around number values from formula of "On Track?" column. Example: =IF(AND([Calc - Days in Training]=0,[Calc - Completion for On Track?]=0),"YES",""). Let me know if it works for you. Commented Feb 1, 2024 at 14:48
  • 1
    @GaneshSanap-MVP That worked! Thank you so much. Commented Feb 1, 2024 at 15:04
  • Great, glad it worked for you. I have added it as an answer below with detailed steps. Commented Feb 1, 2024 at 15:12

1 Answer 1

Reset to default
0

Try following below steps:

  1. Set the return data type of first 2 calculated columns to "Number" from calculated column settings like:

    enter image description here

  2. Then remove double quotes ("") around number values at all places in the formula of "On Track?" column. Example:

=IF(AND([Calc - Days in Training]=0,[Calc - Completion for On Track?]=0),"YES","")
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.