Color Ball Change Formula Help

Anthony D'Ambrosio

Hello Smartsheets Community:

I am needing some help completing a formula that I have written for the color balls to change based on Start and End Dates along with % Complete.

What I would like to happen is when a new project plan template is open and all our % completes are set to 0% that the color ball be gray. Based on the screen shot of the formula below, I have that working correctly.

Then as people mark the % complete with a % the color ball turns green. Again, based on the formula below, that works as well.

Last, when a task is marked 100% I want the color ball to go back to Gray - this is where I am having the issue. As you can see below, I have tow tasks marked 100%, but its returning a green ball. Any help would be greatly appreciated.

Kimberly Loveless

Hi @Anthony D'Ambrosio

It took me a bit to find it but I actually mistyped something in the formula when I was getting it from your original formula, so instead of a a greater than symbol it was a period.

=IF(AND([% Complete]@row<1,(TODAY()-[End Date]@row)>0),"Red",IF(AND((TODAY()-[End Date]@row)>-3,[% Complete]@row<1),"Yellow",IF(OR(AND([% Complete]@row=0,[End Date]@row>TODAY()),[% Complete]@row=100),"Gray",IF([End Date]@row>TODAY(),"Green"

Also just so you know Smartsheet will add the closing parenthesis when you enter a formula. I take advantage of this so that there are not extras if I count wrong 😀

Kimberly Loveless

That clause is in there if it isn't working and one of the yellow or red ones is showing the clauses in the formula may need to be rearranged is all....

=IF(OR(AND([% Complete]@row=0,[End Date]@row>TODAY()),[% Complete]@row=100),"Gray",IF(AND([% Complete]@row<1,(TODAY()-[End Date]@row)>0),"Red",IF(AND((TODAY()-[End Date]@row)>-3,[% Complete]@row<1),"Yellow",IF([End Date]@row>TODAY(),"Green"

Try that one for example, the bolded part is for the 100% complete

Kimberly Loveless

Hi @Anthony D'Ambrosio

I think if you just add an or clause to your formula it will work....

=IF(AND([% Complete]@row<1,(TODAY()-[End Date]@row).0),"Red",IF(AND((TODAY()-[End Date]@row)>-3,[% Complete]@row<1),"Yellow",IF(OR(AND([% Complete]@row=0,[End Date]@row>TODAY()),[% Complete]@row=100),"Gray",IF([End Date]@row>TODAY(),"Green"

Anthony D'Ambrosio

@Kimberly Loveless Thank you so much for answering my question. I did just now try your suggestion and unfortunately, it returns #UNPARSEABLE.

David Tutwiler

Make sure you added close parentheses at the end of Kimberly's great post. They will be required for the formula to resolve. By my quick count I think you'll need 4 ) to get it to resolve.

Anthony D'Ambrosio

@David Talbert , I could not agree with you more. The formula @Kimberly Loveless provide was awesome. I did add the parentheses at the end, but unfortunately, it still comes up with #UNPARSEABLE. I have provided two screen shots below. The top one is showing the #UNPARSEABLE in row. The second screen shot is where I copied and pasted @Kimberly Loveless formula exactly as she has it (with the 4 parentheses). If you see something that I did wrong when copying over the formula, please let me know.

Best,

Anthony D'Ambrosio

Kimberly Loveless

Hi @Anthony D'Ambrosio

It took me a bit to find it but I actually mistyped something in the formula when I was getting it from your original formula, so instead of a a greater than symbol it was a period.

=IF(AND([% Complete]@row<1,(TODAY()-[End Date]@row)>0),"Red",IF(AND((TODAY()-[End Date]@row)>-3,[% Complete]@row<1),"Yellow",IF(OR(AND([% Complete]@row=0,[End Date]@row>TODAY()),[% Complete]@row=100),"Gray",IF([End Date]@row>TODAY(),"Green"

Also just so you know Smartsheet will add the closing parenthesis when you enter a formula. I take advantage of this so that there are not extras if I count wrong 😀

Anthony D'Ambrosio

@Kimberly Loveless

Thank you so much for all your help and your patience!

Quick question, the formula is working great. What do I need to include in the formula to get the color ball to turn back to "Gray" when a % Complete is marked 100%?

Kimberly Loveless

That clause is in there if it isn't working and one of the yellow or red ones is showing the clauses in the formula may need to be rearranged is all....

=IF(OR(AND([% Complete]@row=0,[End Date]@row>TODAY()),[% Complete]@row=100),"Gray",IF(AND([% Complete]@row<1,(TODAY()-[End Date]@row)>0),"Red",IF(AND((TODAY()-[End Date]@row)>-3,[% Complete]@row<1),"Yellow",IF([End Date]@row>TODAY(),"Green"

Try that one for example, the bolded part is for the 100% complete