Color Ball Change Formula Help

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.


Best Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    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
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Kimberly Loveless
    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
    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
    David Tutwiler Overachievers Alumni

    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
    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
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    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
    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
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!