Need some help with a health formula for task risk level analysis
Hello, I am currently working on a formula for the health column in my smartsheet that is based on the the task status and the end date of that particular task. This is what I have so far but what I need is for when the "end date" cell is blank I would like for the health of that task to be blue. The reason is we do not plan to fill in all the dates at one point as are tasks schedule is pretty up in the air but we would like to run analysis on the tasks that are scheduled.
=IF(Progress6 = "Completed", "Green", IF(AND(Progress6 <> "Completed", TODAY() > [Preliminary End Date]6), "Red", IF(AND(Progress6 = "Not Started"), "Blue", IF(AND(Progress6 = "In Progress"), "Yellow"))))
Right now it is:
Red-If past due and labeled as in progress or not started.
Red-If no date is in the end date column... THIS IS WHAT I WANT TO CHANGE.
Yellow - If the task is in progress and not past the due date.
Green - If the task is completed.
Blue - If the task is not started and is not past the due date... I would like to keep this as well.
Help Article Resources
Check out the Formula Handbook template!