Conditional Formatting with Multiple Cells  Green, Yellow, Red Symbols
Hello!
I am having some difficulty writing the correct formula for this sheet:
When the "% Complete" is 100%, I would like the status to be a green check. When the "% Complete" is not 100% and the finish date is still in the future, I would like the yellow hold symbol. And when the "% Complete" is not 100% and the finish date is in the past, I would like the red stop sign/no to populate.
Any insight to help me get on the right track would be greatly appreciated!
Thank you,
Rachel
Best Answers

The formula below checks for boxes in the % Complete column that have a quantity in them and then applies the logic you specified. If the % Complete column is blank, the Staus at a Glance will be blank.
=IF([% Complete]@row = "", "", IF([% Complete]@row = 1, "Yes", IF(AND([% Complete]@row < 1, Finish@row >= TODAY()), "Hold", IF(AND([% Complete]@row < 1, Finish@row < TODAY()), "No", ""))))
Cheers,
John

=IF([% Complete]@row=1,"Green",IF(Finish@row>=TODAY(),"Yellow","Red"))
This formula will first check to see if the Percentage is 100, then Green, Since the only way to get yellow or red is based on that primary condition, we do not need to check it again. If the percent is not 100, it will move to the 2nd if statement and check for the next possible condition which is if the finish date is equal to today or in the future, which will then set it to yellow. If neither the % is 100 or the end date is not in the future, then it will default to red.
Answers

The formula below checks for boxes in the % Complete column that have a quantity in them and then applies the logic you specified. If the % Complete column is blank, the Staus at a Glance will be blank.
=IF([% Complete]@row = "", "", IF([% Complete]@row = 1, "Yes", IF(AND([% Complete]@row < 1, Finish@row >= TODAY()), "Hold", IF(AND([% Complete]@row < 1, Finish@row < TODAY()), "No", ""))))
Cheers,
John

=IF([% Complete]@row=1,"Green",IF(Finish@row>=TODAY(),"Yellow","Red"))
This formula will first check to see if the Percentage is 100, then Green, Since the only way to get yellow or red is based on that primary condition, we do not need to check it again. If the percent is not 100, it will move to the 2nd if statement and check for the next possible condition which is if the finish date is equal to today or in the future, which will then set it to yellow. If neither the % is 100 or the end date is not in the future, then it will default to red.

Thank you both for taking time to provide support! It worked like a charm, John!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!