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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

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.

• Options

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

• Employee
Options

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