IF NOT Functions for Health Symbols

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.

Answers

  • Amber Eakin
    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!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Devin L
    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
    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!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Devin L
    Devin L ✭✭

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


  • Amber Eakin
    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"))))

    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!