Task Health Indicator Formula (IF/AND/OR)

E.Lengermann

Got burnt out looking for my exact scenario in the forums, so looking for direct help. Trying to create a formula to automate my Health column KPIs based on task status and assigned dates.

Status options: Not started, In Progress, Blocked, Complete

Desired Status Colors

Blue: Status = Complete

Red: Status <> Complete AND Past End Date OR Status = "Blocked"

Yellow: Status = "Not Started" AND Start Date < TODAY AND End Date > TODAY

(i.e., task is past it's start date but status hasn't changed from "not started," and it's not yet late because it hasn't hit the due date)

Green: Everything else

Desired outcome (using TODAY as 10/21):

Edit: Figured it out, I had a comma in the wrong place that was messing it up. If helpful for others:

=IF(Status@row = "complete", "Blue", IF(Status@row = "Blocked", "Red", IF(AND(Status@row <> "Complete", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), "Yellow", "Green"))))

Darla Brown

Hi,

Just making sure - you have fixed the problem? Or do you still need help?

Thanks