Help! Trying to edit the automation of RYG balls!


Hello All! I am stuck trying to fix the automation of my RYG balls in Smartsheets! I need to add one more IF formula to be the formula below so that if a task has not yet been started, the ball will show up as gray.  You can see from the screenshot below that all the other balls are working except when the task has yet to have approval to move forward and therefore is "Not Started" under the status column, it still shows up as red. Can you please help me fix the formula below to add this feature? Thank you! 

=IF(Done1 <> 1, IF(TODAY() - [Due Date]1 > -1, "Red", IF(TODAY() - [Due Date]1 > -4, "Yellow", "Green")))

Screenshot 1.JPG


  • rissa
    edited 02/21/19



    Hopefully this works I tested it with my sheets so fingers crossed.

    First follow the picture and then change the code to:

    =IF(Done1 <> 1, IF(Status1 = "Not Started", "Gray", IF(TODAY() - [Due Date]1 > -1, "Red", IF(TODAY() - [Due Date]1 > -4, "Yellow", "Green"))))





  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Rissa is correct. To further explain... Nested IF's work in the order they are typed in. If the first IF is true, then it won't check to see if any others are. Nesting them in order of highest priority first is definitely a must.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!