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.

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. Feb 1 at 8:52
  • @GaneshSanap-MVP I have added the column names and formulas to the original question.
    – Dustin
    Feb 1 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. Feb 1 at 14:48
  • 1
    @GaneshSanap-MVP That worked! Thank you so much.
    – Dustin
    Feb 1 at 15:04
  • Great, glad it worked for you. I have added it as an answer below with detailed steps. Feb 1 at 15:12

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.