Need help correcting RYGB formula ASAP

victoria.anzalone76406

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?

 

Paul Newcome

My original post keeps getting blocked (I have reached out to SS about this already), so in the meantime I have typed my original post into a Word doc, taken a screenshot, and attached said screenshot here...

victoria.anzalone76406

Thank you! This looks much closer to what I'm looking for. I tested it out, though, and I'm getting the"unparseable" error:

See attached for how I entered it in the cell. (I'm also getting block when I try to type out the formula in my post!)

I've checked it very carefully and I think I have spaces, parentheses, brackets where they need to be. Any ideas why it wouldn't be working?

Paul Newcome

Your first reference to the Working Start column is missing a space.

 

[WorkingStart]

vs

[Working Start]

victoria.anzalone76406

Ahh, I knew it would be something silly like that. Thank you so much!

Paul Newcome

No worries. Happy to help!