Traffic Light Formula Based on Date and % Complete
Hello,
I am trying to create a health status formula using the traffic light: Red, Yellow, Green Blue symbols. I would like the status to update based on the Deadline as well as the % Complete. The second set of conditions within the formula will not return a result. Here is the formula:
=IF([% Complete]@row = 1, "Blue", IF([Start Date]@row > TODAY(), "Green", IF(Deadline@row < TODAY(14), IF([% Complete]@row <= 0.25, "Red", IF([% Complete]@row <= 0.5, "Yellow", "Green", IF(Deadline@row = TODAY(), IF([% Complete]@row >= 0.75, "Green", IF(Deadline@row < TODAY(), "Red", IF(Deadline@row > TODAY(14), IF([% Complete]@row <= 0.25, "Yellow", IF([% Complete]@row >= 0.5, "Green", "Green")))))))))))
Every IF statement after Deadline@row < TODAY(14) does not return data. I would love some help with whatever I'm doing wrong.
Thanks,
Liane
Answers
-
Hi Liane,
Looks like your trying to test for two values in certain cases. If so, you should use the AND function. Also, there was one too many colors after [% Complete]@row <= 0.5, i figured it needed to be Yellow.
Assuming my statement above is correct your formula may work if it was as follows:
=IF([% Complete]@row = 1, "Blue", IF([Start Date]@row > TODAY(), "Green", IF(AND(Deadline@row < TODAY(14),[% Complete]@row <= 0.25), "Red", IF(AND(Deadline@row < TODAY(14),[% Complete]@row <= 0.5), "Yellow", IF(AND(Deadline@row = TODAY(),[% Complete]@row >= 0.75), "Green", IF(Deadline@row < TODAY(), "Red", IF(AND(Deadline@row > TODAY(14),[% Complete]@row <= 0.25), "Yellow", IF([% Complete]@row >= 0.5, "Green", "Green"))))))))
copy paste it in and give it a shot!
If the results aren't what you expect it could be for one of two reasons. 1.) I did not understand your work flow. 2.) the logical tests need to be arranged in a different order so that they trigger in the proper order.
Happy Trails!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!