Welcome!

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

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

Find more posts tagged with

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Week Number Year to Year
I've got the following formula in a Check Box column to check when something is due in the Next 3 weeks. =IFERROR(IF(AND(WEEKNUMBER([Projected Cleaning Date]@row ) = WEEKNUMBER(TODAY()) + 3, YEAR([Projected Cleaning Date]@row ) = YEAR(TODAY())), 1), "") I have them for 2 weeks, 3 weeks, 4 weeks, and 5 weeks. These stopped…
Use Sheet Summary as cell reference for a different sheet
I'm using salesforce connector to pull my team's hours information in real-time. The Salesforce connector sheet contains sheet summaries that I'd like to use a cell reference for a different sheet. I can't seem to find the best way or formula to do this. I don't want to use a dashboard with report widgets because I prefer…
Issues with IF formula
I have two formulas which work well independently, but when I combine them they don't. formula 1: =IF(YEAR([Joined date]@row ) = 2025, JOIN(COLLECT({Membership Survey 2025 - Experience}, {Membership Prioritisation Survey 2025 - Org}, [Organisation name]@row ))) formula 2: =IF(YEAR([Joined date]@row ) < 2025,…