Color Symbol formula not working

Emily T.

Hi!

I'm trying create a formula that shows the following:

Blue - Status is complete

Green - Estimated completion day it today or in the future

Yellow - Estimated completion date is between 1-7 days past the due date it is yellow

Red - Estimated completion date is 8+ days past due it is Red.

Gray - if there are no dates.

Below is the formula. The blue, green, and yellow work correctly. The red will work between 8-14 days past due but then goes to gray. I need it to stay red and this is where I'm stumped. I've tried changing the IF([Estimated Completion Date]@row >= TODAY(-14), "Red", to TODAY (-300), "red" and instead of giving me the red symbol, it just says "red" on the sheet I need it to work and on the other sheet I troubleshooted on the red symbol appears.

Also, the Health column is not defaulting to Gray.

=IF(Status@row = "Completed", "Blue", IF([Estimated Completion Date]@row >= TODAY(), "Green", IF([Estimated Completion Date]@row >= TODAY(-7), "Yellow", IF([Estimated Completion Date]@row >= TODAY(-14), "Red", "Gray"))))

Thoughts?

Thank you,

Emily

Nick Korna

Hi @Emily T.

I think this should do what you're after:

=IF(Status@row = "Completed", "Blue", IF(AND([Estimated Completion Date]@row <= TODAY(-8), [Estimated Completion Date]@row <> ""), "Red", IF(AND([Estimated Completion Date]@row <= TODAY(-1), [Estimated Completion Date]@row >= TODAY(-7)), "Yellow", IF([Estimated Completion Date]@row >= TODAY(), "Green", "Gray"))))

Sample data/output:

Hope this helps, but if there are any problems/questions then just post! 🙂

Nic Larsen

Try this: =IF([Status]@row = "Completed", "Blue", IF([Estimated Completion Date]@row >= TODAY(8), "Red", IF(AND([Estimated Completion Date]@row >= TODAY(1), [Estimated Completion Date]@row <= TODAY(7)), "Yellow", IF([Estimated Completion Date]@row >= TODAY(), "Green", "Gray"))))

Emily T.

Hi Nic. Thank you for the formula. It does add the "gray" in those areas missing dates, which is what I wanted, but in those that should be "red" it's making those gray, also, where I have the Estimated Completion Dates in the row that are past due. Thoughts?

Nick Korna

Hi @Emily T.

I think this should do what you're after:

=IF(Status@row = "Completed", "Blue", IF(AND([Estimated Completion Date]@row <= TODAY(-8), [Estimated Completion Date]@row <> ""), "Red", IF(AND([Estimated Completion Date]@row <= TODAY(-1), [Estimated Completion Date]@row >= TODAY(-7)), "Yellow", IF([Estimated Completion Date]@row >= TODAY(), "Green", "Gray"))))

Sample data/output:

Hope this helps, but if there are any problems/questions then just post! 🙂