# IF NOT Functions for Health Symbols

Options
✭✭

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.

• ✭✭✭✭✭✭
Options

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!

Amber Eakin, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

@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!

Amber Eakin, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Amber Eakin, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!