Help with IF/AND formula for symbols
Hello! I am working on a formula to display red/yellow/green circles for project health, but I want to filter out projects that are closed. I have the formula working but when I try to add the AND part to filter out closed projects, I keep getting errors.
Here's what I have:
=IF(TODAY() = [Estimated Finish]@row, "Yellow", IF(TODAY() > [Estimated Finish]@row, "Red", IF(TODAY() < [Estimated Finish]@row, "Green")))
If the estimated finish date is today, health is yellow. If date is in the past, health is red. If in future, health is green. I want to add a filter to only show projects where % complete is < 1 (not show completed projects).
Would love some help  thank you!
Best Answer

Hello Lisa,
You actually don't need to use AND function here. Definitely you could use AND, but that would make the formula a little bit complex. What I suggest, you can filter out the completed projects at first just before using your formula. So, there will be another IF function at the beginning. Try using the following formula:
=IF([%complete]@row<1, (IF(TODAY() = [Estimated Finish]@row, "Yellow", IF(TODAY() > [Estimated Finish]@row, "Red", IF(TODAY() < [Estimated Finish]@row, "Green")))), "")
Answers

Hello Lisa,
You actually don't need to use AND function here. Definitely you could use AND, but that would make the formula a little bit complex. What I suggest, you can filter out the completed projects at first just before using your formula. So, there will be another IF function at the beginning. Try using the following formula:
=IF([%complete]@row<1, (IF(TODAY() = [Estimated Finish]@row, "Yellow", IF(TODAY() > [Estimated Finish]@row, "Red", IF(TODAY() < [Estimated Finish]@row, "Green")))), "")

AAH, that worked!!! I was overthinking it and your solution worked perfectly. Thank you!! 😁
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 215 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!