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")))), "")
Senior Business Intelligence Analyst
Augmedix Bangladesh
(A Commure Company)
shimanta@augmedix.com
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")))), "")
Senior Business Intelligence Analyst
Augmedix Bangladesh
(A Commure Company)
shimanta@augmedix.com -
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
- Customer Resources
- 67K Get Help
- 442 Global Discussions
- 154 Industry Talk
- 503 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 79 Community Job Board
- 512 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!