Use date and status to define RYG balls
Hey all,
Trying to make a simple task management sheet, using today's date and the task status to define a Red, Yellow, Green or Grey ball.
If end date is today or later, AND status is not complete or cancelled, green
If end date is today -1, AND status is not complete or cancelled, yellow
If end date is today -2 or further in the past, AND status is not complete or cancelled, red
If status is complete or cancelled, grey
I'm assuming I need to combine IF OR AND functions... but, not getting very far. Your help would be amazing!! Here's what I have at the moment....
=IF(OR(AND(Status@row <> "Complete", [End Date]@row = TODAY(), "Yellow")))
Best Answer
-
This formula will do it
=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "Cancelled", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() = -1, "Yellow", IF([End Date]@row - TODAY() = -2, "Red", "N/A")))))
You can check for Complete and the Cancelled Status to start, thereby eliminating the need to use the AND/OR Statements. This is because as soon as the first statement is met nothing else in the formula triggers.
Note that as described:
- I wrote the formula to display "Cancelled" in the RAG column for Cancelled jobs. This could be any text you like or it could be empty by using two quotes (""),
- Statuses of "Not Started," "On Hold", and "In Progress" will behave the same,
- There is no status associated with any non-Completed/Cancelled task that has an end date in the future.
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
Answers
-
This formula will do it
=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "Cancelled", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() = -1, "Yellow", IF([End Date]@row - TODAY() = -2, "Red", "N/A")))))
You can check for Complete and the Cancelled Status to start, thereby eliminating the need to use the AND/OR Statements. This is because as soon as the first statement is met nothing else in the formula triggers.
Note that as described:
- I wrote the formula to display "Cancelled" in the RAG column for Cancelled jobs. This could be any text you like or it could be empty by using two quotes (""),
- Statuses of "Not Started," "On Hold", and "In Progress" will behave the same,
- There is no status associated with any non-Completed/Cancelled task that has an end date in the future.
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
-
Dan, this is AMAZING! Thanks a lot :)
I wonder if I can go one step further.... I'd like yellow to show for 1 to 4 days delay, and red to show for 5 days plus delay. And ideally, I'd like that to be working days. Is that possible??
I added < 0 and < -4 - and it seems to work for yellow, but not for red... any clue @Dan Palenchar ?
=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "Cancelled", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() < 0, "Yellow", IF([End Date]@row - TODAY() < -4, "Red", "N/A")))))
Help Article Resources
Categories
Check out the Formula Handbook template!