RYG Balls-can't figure out

03/28/18 Edited 12/09/19

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.

Thoughts?

Popular Tags:

Comments

  • Chris McKayChris 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)

Sign In or Register to comment.