RYGB Status not turning red

Options

I'm using the formula below and based on the date (03/08/21), row 2 Health should be red. Any thoughts?

=IF(FinalDeckReceived@row = 1, "Blue", IF([Deck Due Date]@row <= TODAY(+7), "Yellow", IF([Deck Due Date]@row > TODAY(-7), "Green", IF([Deck Due Date]@row > TODAY(), "Red", "Blue"))))



Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Bob Kernan

    Logic formulas read instructions from the left-to-right and stop as soon as there is a statement that matches the criteria. Since in the screen capture above the date (03/08/21) is less than Today + 7 days, then it's Yellow. The formula doesn't check the rest of the criteria since the date is indeed in the past.

    You'll want to add in starting dates as well as ending dates... meaning to look between two dates as a range instead of just providing one criteria. For example, Yellow might be less than Today + 7 , but also, greater than Today.

    Try this:

    =IF(FinalDeckReceived@row = 1, "Blue", IF(AND([Deck Due Date]@row <= TODAY(+7), [Deck Due Date]@row > TODAY()), "Yellow", IF(AND([Deck Due Date]@row > TODAY(-7), [Deck Due Date]@row < TODAY()), "Green", IF([Deck Due Date]@row < TODAY(), "Red", "Blue"))))

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Bob Kernan

    I hope you're well and safe!

    Not sure I follow.

    Your example looks correct, but I'm guessing that you want the rows before today that aren't checked to be red.

    Correct?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Bob Kernan
    Options

    I want any date that is past the Deck Due Date to turn Red

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Bob Kernan

    Logic formulas read instructions from the left-to-right and stop as soon as there is a statement that matches the criteria. Since in the screen capture above the date (03/08/21) is less than Today + 7 days, then it's Yellow. The formula doesn't check the rest of the criteria since the date is indeed in the past.

    You'll want to add in starting dates as well as ending dates... meaning to look between two dates as a range instead of just providing one criteria. For example, Yellow might be less than Today + 7 , but also, greater than Today.

    Try this:

    =IF(FinalDeckReceived@row = 1, "Blue", IF(AND([Deck Due Date]@row <= TODAY(+7), [Deck Due Date]@row > TODAY()), "Yellow", IF(AND([Deck Due Date]@row > TODAY(-7), [Deck Due Date]@row < TODAY()), "Green", IF([Deck Due Date]@row < TODAY(), "Red", "Blue"))))

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!