Need help correcting RYGB formula ASAP


My colleague set up a formula I'm using for RYGB column and I need some help interpreting it to figure out why it isn't behaving exactly the way I want it to:

=IF(ISDATE([Actual End]@row), "Blue", IF([Working Start]@row > TODAY(), "", (IF([Working End]@row < TODAY(), "Red", (IF(ISDATE([Actual Start]@row), "Green", "Yellow")))))) 

We have working columns and actual columns. I want the following behavior:

If Actual End has a date, BLUE. (i.e., Complete)


If all of the date columns have no date, RED. (i.e., Needs to be scheduled)


If Working End is in the past and Actual End has no date, RED (i.e., Late to end)


If Working Start is in the past and Actual Start has no date, YELLOW (i.e., Late to start)


If Working Start is in the future, blank (i.e., Scheduled, but neither on time nor late)


GREEN (i.e., On time)

I had tested this out and got the results I expected for the most part, but I just saw the attached situation occur and I'm seeing a Yellow where I was expecting a Blank (no ball).

Another indicator that something is off is that we also have the Red Flag set up to turn on for either late to start or late to end (based on the same types of expressions above). In the screenshot, you see the Red Flag is not on for the task that's Yellow. When the RYGB formula is working correctly, we should have a flag on anytime the status is Red or Yellow. (That said, I might just simplify my At Risk formula to ignore the date columns and just be 1 if RYGB is Red or Yellow, else 0.)

Can someone suggest a revised RYGB formula to get me the outcome I wanted?


Current behavior.PNG



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!