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"))))))))))
Comments
-
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"))))))))))
-
Unfortunately when I list it that way it just says that is an incorrect argument set
-
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"))))))))
-
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.
-
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"))))))))
-
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.
-
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")))))))
-
Great. Glad I helped you get it on the right track.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!