Red Flag for Overdue Tasks, without also Flagging non-dated ones
We are trying to use a formula to do the following:
•tasks that have been cancelled are gray
•tasks that are past their due date are red
•tasks whose due dates come up within the next 5 days and are not yet complete are in Yellow
•tasks which are already completed or whose start date hasn't hit, and are due in 14 days are Green
It now red flags every row that doesn't have a date in yet, and I can't figure out how to make it stop.
Right now we are using:
=IF(AND(Finish@row < TODAY(), Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(5), Status@row <> "Complete"), "Yellow", IF(AND(Status@row <> "Complete", Start@row > TODAY(), Finish@row < TODAY(14)), "Green", IF(AND(Status@row <> "Complete", Start@row > TODAY(14), Finish@row > TODAY(14)), "Gray", "Green"))))
Anyone have any thoughts? Btw, I am a complete Smartsheets novice, and struggle to even read a formula, so the more detailed, the better
Help Article Resources
Check out the Formula Handbook template!