IF, AND, OR for task health based on target finish date and status
![Tmaid](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I'm trying to build a IF forumual in the health column that will have all the conditions built out below.
I use the conditional formatting as a workaround, the conditional formatting is not the end goal.
Column Names
"Health"
"Planned Finish"
"True % Complete"
"Status"
This is what I've build out so far but I'm not sure if I'm on the right track
=IF(AND(Status@row = "Complete", [True % Complete]@row = "100%", "Blue")), IF(AND(Status@row = "Complete", [True % Complete]@row > "100%", "Red")), IF(Status@row <> 1, IF(TODAY() - [Planned Finish]@row > 0, "Red", IF(TODAY() - [Planned Finish]@row > -14, "Yellow", IF(TODAY() - [Planned Finish]@row < 14 = "Green")))), IF(AND([True % Complete]@row = "", [Planned Finish]@row > 0, "Red"))
Answers
-
Try this:
=IF(Status@row = "Complete", IF([True % Complete]@row = 1, "Green", "Red"), IF([Planned Finish]@row< TODAY(), "Red", IF([Planned Finish]@row<= TODAY(14), "Yellow", "Green")))
-
Thank you that worked for part of the condistions. Let me see if I can finished build out the ones for status based on your outline above. ;-)
-
Which ones aren't working or are missing?
Here's a breakdown of the formula:
=IF(Status@row = "Complete", IF([True % Complete]@row = 1, "Blue", "Red"), IF([Planned Finish]@row< TODAY(), "Red", IF([Planned Finish]@row<= TODAY(14), "Yellow", "Green")))
If the Status is Complete --> If the [True % Complete] is 100% then Green. Otherwise Red. This takes care of the first two (although I see now it was supposed to be blue and not green which has been corrected in the above).
If the Status is not Complete and the [Planned Finish] is in the past then Red. This takes care of the last four.
Then we have Yellow for if the [Planned Finish] is in the next 14 days.
And Green for everything else which should just be when the [Planned Finish] is more than 14 days in the future.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!