Formula

Melitta

Having difficulty with a formula setting task state (Not Started, In Progress, Complete, On Hold) depending upon how the columns on either side are set - Status/RYGB and % Complete.

=IF(AND(Status@row=“B”, [% Complete]@row =1),"Complete”,IF(AND(Status@row= "G”,[% Complete]@row=0), "Not Started”, IF(AND(Status(G,Y,R)@row= “G”, “Y”, “R”, [% Complete]@row < 0),”In Progress",IF(AND([On Hold]@row = 1), "On Hold”)))

Thanks

Bassam Khalil

Hi @Melitta

Hope you are fine, as i understand that you use for status column BGYR symbols column type if so then try the following formula.

=IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress", "On Hold")))

Melitta

Hello @Bassam.M Khalil

Thank you that is very helpful, and gets me a closer to the final solution. It also makes me realize that I left out a piece.

There is another column: On Hold. It's a checkbox. If it's checked, I want the Task State to be "On Hold". Unchecked and it should follow the rest of the formula.

How do I combine them?

=IF([On Hold]@row = 1, "On Hold")

=IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress")))

Thanks,

Melitta

Bassam Khalil

Hi @Melitta

Hope you are fine, as i understand that you use for status column BGYR symbols column type if so then try the following formula.

=IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress", "On Hold")))

Melitta

Hello @Bassam.M Khalil

Thank you that is very helpful, and gets me a closer to the final solution. It also makes me realize that I left out a piece.

There is another column: On Hold. It's a checkbox. If it's checked, I want the Task State to be "On Hold". Unchecked and it should follow the rest of the formula.

How do I combine them?

=IF([On Hold]@row = 1, "On Hold")

=IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress")))

Thanks,

Melitta

Bassam Khalil

@Melitta

You are welcome and am happy you find it helpful, Please help the Community by marking it as the accepted answer.