Using IF/AND/OR and Red Yellow Green

T Hudson · 04/25/22

I'm probably just fried at this point, but I'm having a problem. I'm trying to use a Red, Yellow, Green system to highlight when users do not respond within a certain period of time. I have a column calculating "Days Since Submission". I also have a status column. I want the warning column to remain green if it has been more than 2 days since submission and the status is "Not Started". If it has been 3-5 days since submission, I'd like it to turn yellow. If it is more than 5 days and the status is still "Not Started", I would like it to turn red. The current formula I have is this:

=IF([DAYS SINCE SUBMISSION]@row < 2, "Green", IF(AND([DAYS SINCE SUBMISSION]@row > 5, Status@row = "Not Started"), "Red", "Yellow"))

However, there is a logic flaw in this. If it is something other than "Not Started" and over 5 days, it remains yellow. Clearly this isn't the intention. I know there should some sort of possible "OR" logic or maybe I need to be more specific, but I need someone who can help. Thanks!

Jon Mark H · 04/25/22

Hey @T Hudson ,

Looks like you were almost there - you just need to add a condition for the Yellow color:

=IF(AND([DAYS SINCE SUBMISSION]@row < 2, Status@row = "Not Started"), "Green", IF(AND([DAYS SINCE SUBMISSION]@row > 5, Status@row = "Not Started"), "Red", IF(Status@row = "Not Started", "Yellow", "")))

I added the condition of "Not Started" to the Green as well, so if the status changes at any time, the color disappears. Obviously you can change that back to your original though if you prefer.

Let me know if that fixes your problem or if you have any other issues!

-Jon Mark

Jon Mark H · 04/25/22