# Formula Help!

Options
edited 09/08/22

Hello!

I have been working with this formula for hours and cannot figure out what I am doing wrong. I am trying to automate the health bubbles to turn Red, Yellow, Green and Blue based on where the task is in the start and due date. I got that part figured out. BUT I also want it to turn red if the "At Risk" column is flagged. I am using the IF(OR) formula on the red option so it turns red if the due date has passed OR if the at risk is marked. What am I doing wrong?!?!?! Thank you!

Tags:

Options

That worked! I ended up with the below and it fixed the issue. Thank you so much!

=IF([At Risk]3 = 1, "Red", IF(AND([Due Date]3 > TODAY(14), [Start Date]3 < TODAY(1)), "Green", IF(AND([Due Date]3 >= TODAY(0), [Due Date]3 <= TODAY(13)), "Yellow", IF([Due Date]3 < TODAY(0), "Red", IF([Start Date]3 > TODAY(0), "Blue")))))

• ✭✭✭✭✭✭
Options

Try moving the "Red" IF to the beginning. Nested IF statements read from left to right and stop a the first true value. This means if it is true for "Green" or "Yellow" it is going to stop there and not even be evaluated for the "Red".

Options

That worked! I ended up with the below and it fixed the issue. Thank you so much!

=IF([At Risk]3 = 1, "Red", IF(AND([Due Date]3 > TODAY(14), [Start Date]3 < TODAY(1)), "Green", IF(AND([Due Date]3 >= TODAY(0), [Due Date]3 <= TODAY(13)), "Yellow", IF([Due Date]3 < TODAY(0), "Red", IF([Start Date]3 > TODAY(0), "Blue")))))

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!