Color Symbol formula not working

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

Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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! 🙂

Answers

  • Nic Larsen
    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.
    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
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!