Health Status Formula - IF(AND formula for certain deadline date and stage of task

Ashley Archibeque

I am trying to write a health formula with the below parameters. I have been playing with the below formula for hours and still cannot get it to match the criteria I need it to below. Any help or suggestions would be GREATLY appreciated!

Health Code Criteria:

If task is "Completed" = Green, If task is "Blocked" = red, If task is "Deferred" = Gray, If task is "Not started" = Gray AND

If a task is 7 days PAST deadline and is not completed = red

If a task is 2 weeks BEFORE deadline and completed = yellow (When it is marked <> Completed all "Not Started" tasks that are within 2 weeks of due date remain gray..this is one error using the below formula).

If a task is 1 day past deadline and not completed = yellow (I can't get this to work AND the 2 weeks before and not completed...how do I do a date range here?)

This is my best attempt at the formula....anyone have any ideas what I am doing wrong?

=IF((Stage@row = "Completed"), "Green", IF((Stage@row = "Blocked"), "Red", IF((Stage@row = "Deferred"), "Gray", IF((Stage@row = "Not Started"), "Gray", IF(AND(Deadline@row < TODAY(+5), Stage@row <> "Completed"), "Red", IF(AND(Deadline@row > TODAY(-10), Stage@row <> "Completed"), "Yellow", IF(AND(Stage@row <> "Completed", Deadline@row > TODAY(+30), "Green", IF(AND(Deadline@row < TODAY(+14), Stage@row = "Not Started", "Yellow", "Green"))))))))))

Mike Wilday

Hi Ashley,

At brief glance, the initial error is with the set up of your formula... it's critical to understand that an IF formula follows the logical flow of your formula and checks each statement individually. Once the formula finds a match it will stop looking at the rest of the IF statements. So in your case... Your first statement will prevent your requirement of the 2 weeks before deadline and completed from firing. Try this brief re-arrangment and let me know if it works.

=IF((Stage@row = "Blocked"), "Red", IF((Stage@row = "Deferred"), "Gray", IF(AND(Deadline@row < TODAY(+5), Stage@row <> "Completed"), "Red", IF(AND(Deadline@row > TODAY(-10), Stage@row <> "Completed"), "Yellow", IF(AND(Stage@row <> "Completed", Deadline@row > TODAY(+30), "Green", IF(AND(Deadline@row < TODAY(+14), Stage@row = "Not Started", "Yellow", IF((Stage@row = "Completed"), "Green", IF((Stage@row = "Not Started"), "Gray", "Green"))))))))))

Ashley Archibeque

Unfortunately when I list it that way it just says that is an incorrect argument set

Mike Wilday

Okay, I also saw some oddities with your ( marks.... try this cleaned up version:

=IF(Stage@row = "Blocked", "Red", IF(Stage@row = "Deferred", "Gray", IF(AND(Deadline@row < TODAY(+5), Stage@row <> "Completed"), "Red", IF(AND(Deadline@row > TODAY(-10), Stage@row <> "Completed"), "Yellow", IF(AND(Stage@row <> "Completed", Deadline@row > TODAY(+30)), "Green", IF(AND(Deadline@row < TODAY(+14)), Stage@row = "Not Started", "Yellow", IF((Stage@row = "Completed"), "Green", IF(Stage@row = "Not Started", "Gray", "Green"))))))))

Ashley Archibeque

I still says incorrect argument set; I feel like it has something to do with the ascending order of the date ranges possibly but have listed them multiple ways and it still can't seem to get this to work.

Mike Wilday

Nope, that was me... I misplaced a closing parenthesis.

Try this one:

=IF(Stage@row = "Blocked", "Red", IF(Stage@row = "Deferred", "Gray", IF(AND(Deadline@row < TODAY(+5), Stage@row <> "Completed"), "Red", IF(AND(Deadline@row > TODAY(-10), Stage@row <> "Completed"), "Yellow", IF(AND(Stage@row <> "Completed", Deadline@row > TODAY(+30)), "Green", IF(AND(Deadline@row < TODAY(+14), Stage@row = "Not Started"), "Yellow", IF(Stage@row = "Completed", "Green", IF(Stage@row = "Not Started", "Gray", "Green"))))))))

Ashley Archibeque

The formula works but not the way I need it to. Using the above formula, it shows tasks that are 14 days out or more and not started as yellow when I need tasks due within 14 days or less and not started to only be yellow and all tasks outside the 14 day window to remain gray. I've also played with the less than and greater than's to no avail.

Ashley Archibeque

I figured it out: I needed to put a date range in for yellow vs two separate IF(AND statements! Figured I'd share in case this is useful for anyone else someday!

=IF(Stage@row = "Blocked", "Red", IF(Stage@row = "Deferred", "Gray", IF(AND(Deadline@row < TODAY(+14), Deadline@row > TODAY(+4), Stage@row <> "Completed"), "Yellow", IF(AND(Deadline@row < TODAY(+5), Stage@row <> "Completed"), "Red", IF(AND(Stage@row <> "Completed", Deadline@row < TODAY(-30)), "Green", IF(Stage@row = "Completed", "Green", IF(Stage@row = "Not Started", "Gray", "Green")))))))

Mike Wilday

Great. Glad I helped you get it on the right track.

Chelsea Hirschton

I have a follow up formula I need help on. I'm getting an unparseable error on the following:

=IF(Status@row = "At Risk", "Red", IF(Status@row = "Concern", "Yellow", IF(AND(Start Date@row < TODAY, Status@row = "Not Started"), "Yellow", IF(AND(End Date@row < TODAY, OR(Status@row <> "Complete", Status@row <> "Complete-N/A")), "Red", IF(Status@row = "Not Started", "", IF(Status@row = "On Track", "Green", IF(OR(Status@row = "Complete", Status@row = "Complete - N/A"), "Blue", "")))))))

@Mike Wilday Any suggestions for me?