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)
else
If all of the date columns have no date, RED. (i.e., Needs to be scheduled)
else
If Working End is in the past and Actual End has no date, RED (i.e., Late to end)
else
If Working Start is in the past and Actual Start has no date, YELLOW (i.e., Late to start)
else
If Working Start is in the future, blank (i.e., Scheduled, but neither on time nor late)
else
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?
