Help with IF/AND formula for symbols

Lisa Wood

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!

Shimanta Roy

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")))), "")

Shimanta Roy

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")))), "")

Lisa Wood

AAH, that worked!!! I was overthinking it and your solution worked perfectly. Thank you!! 😁