IF NOT Functions for Health Symbols

Devin L

I need help with this formula function (Red, Yellow, Green Gray).

If the STATUS is not complete and past due = Red

If the STATUS is not complete and due in 10 days = Yellow

If the STATUS is not complete and due in <10 days = Green

If the STATUS is complete = Gray.

Amber Eakin

This might work for you!

=IF(AND(Status@row <> "Complete", [Completion Date]@row < TODAY()), "Red", IF(AND(Status@row <> "Complete", [Completion Date]@row > TODAY(+10)), "Yellow", IF(AND(Status@row <> "Complete", [Completion Date]@row < TODAY(+10)), "Green", IF(Status@row = "Complete", "Gray"))))

I hope it helps!

Devin L

Thank you Amber for the help. I have it working but can't get the yellow and green functions to work properly. Everything defaults to the yellow function.

Amber Eakin

@Devin L , change all the "Finish37" to "Finish@row" and "Status37" to "Status@row". You're currently only look at one row, so @row will change the formula to check the current row. Let me know if that fixes it!

Devin L

@Amber Eakin still has an issue between the green and yellow.

Amber Eakin

Let's flip a few of the nested IFs. Can you try this one?

=IF(AND(Status@row <> "Complete", Date@row < TODAY()), "Red", IF(AND(Status@row <> "Complete", Date@row < TODAY(+10)), "Green", IF(AND(Status@row <> "Complete", Date@row > TODAY(+10)), "Yellow", IF(Status@row = "Complete", "Gray"))))