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

Options

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"))))))))))

• ✭✭✭✭✭✭
Options

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"))))))))))

• Options

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

• ✭✭✭✭✭✭
Options

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"))))))))

• Options

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.

• ✭✭✭✭✭✭
Options

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"))))))))

• Options

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.

• edited 05/27/20
Options

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")))))))

• ✭✭✭✭✭✭
Options

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

• Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!