RYG Balls-can't figure out

1bad78ta

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?

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)