RYGB Status not turning red

Bob Kernan

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

Genevieve P.

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

Andrée Starå

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!

Bob Kernan

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

Genevieve P.

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

Bob Kernan

Thank you Genevieve