Using IF/AND/OR and Red Yellow Green

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!
Best Answer
-
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
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives