Trying to format the RYG balls according to Status and Due Date

I have formatted my RYG balls to change according to the status of the task; Not Started = Grey, In Progress = Yellow, Complete = Green, and so on. I want Red = Not completed, and also past due.

This is what I have:

=IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(AND(today@row - [Due Date]@row >= 0, Status@row <> "Complete", "Red")))))

I have tried several permutations of this, this is just the most recent one.

Best Answers


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/09/20

    You are actually VERY close.

    In your original formula, move one of those closed parenthesis from the end to before the comma right before "Red".

    =IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(AND(today@row - [Due Date]@row >= 0, Status@row <> "Complete"), "Red"))))

    However... You actually don't need the AND function. Because you have logic for if the status is "Complete", anything that runs after that automatically assumes that the status is not complete. Once a nested IF finds a true value, it will not run anything after it, so it is implied that all previous IF's are false.

    =IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(today@row - [Due Date]@row >= 0, "Red"))))

    Also... What exactly is in "today@row"?

  • rbuckner
    rbuckner ✭✭✭


    thank you for your help, but it doesn't quite seem to have solved my problem.

    I input the corrections, and all it will do is make the RYG ball "red" in empty rows. But I was hoping that using <>"Complete" would mean that if the value were anything other than "Complete", that the ball would be Red once the due date has passed. With that first correction you gave me, I have a row with the status as "In Progress" and the due date has passed, but the ball remains yellow rather than becoming red.

    Also, the today@row was something I tried when I couldn't get it to work, I thought if I subtracted that value from the due date column value, it would work better than using ...TODAY()

  • rbuckner
    rbuckner ✭✭✭

    Awesome, that works!

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!