Formula Help!

LMcMillan

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!

LMcMillan

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

Paul Newcome

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

LMcMillan

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

Paul Newcome

Happy to help. 👍️