Red Flag for Overdue Tasks, without also Flagging non-dated ones

kseosentral

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

BUT....

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

Mike TV

@kseosentral

I'm guessing if Start is blank you want the Health color to be green?

=IF(ISBLANK(Status@row), "Green", 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")))))

I'm not sure if that will have the desired results though. I get green for everything no matter the date ranges I enter or whether a Status field has Completed or not.

Mike TV

@kseosentral

What constitutes a task that has been Cancelled? I don't get that from your formula. All conditions from your Status column are either equals Complete or does not equal Complete. The part of the formula for the Gray color (which you said would be color used for Cancelled) is just looking for Status doesn't equal Complete and has a Start and Finish date two weeks from now.

What are you using to determine a Due Date? I don't see a column for that or any reference in your formula. There's a Start and a Finish column but those aren't due dates.

kseosentral

Thanks Matt-- The finish date on this project is the same as the due date. I changed the =IF(ISBLANK(Status@row) to =IF(ISBLANK(Start@row) and it's working for what we want-- and you're right, the cancelled portion was missing earlier on. Thanks for the help!