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
Check out the Formula Handbook template!