RYG Balls-can't figure out

edited 12/09/19 in Formulas and Functions

I've looked and cannot solve a problem I had solved before...

I have a checkbox to denote when a task is completed.

I have a due date.

My problem is that I want my RYG to be green if the box is checked (that seemed easy to do) and Yellow if the due date is within 7 days AND the the box is not checked and Red if the due date is passed and the box is not checked.  Just can't get the IF AND function correct.  I can get the TODAY function to work but cannot seem to get the nested IF to work.




  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Assuming column names are Due Date, Complete and Status:

    =IF(OR(Complete1, [Due Date]1 > TODAY()), "Green", IF(AND([Due Date]1 >= (TODAY() - 7), [Due Date]1 <= (TODAY()), Complete1 <> 1), "Yellow", IF(AND([Due Date]1 < (TODAY() - 7), Complete1 <> 1), "Red", "")))

    This captures some additional situations your above description didn't (e.g. if the box is not checked and the due date is in the future)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!