Traffic light formula

Simi

Feel like this should be a relatively simple formula, but can't get it to work!

I want to do this:

If content due date is greater than 4 workdays from today OR if content submitted is checked return green

If content due date is between 1-4 workdays from today and content submitted is not checked return yellow

If content due date is equal to or past today and content submitted is not checked return red

=IF(OR([Content Submitted]@row = 1, TODAY() > WORKDAY([Content Due]@row, 4), "Green")), IF(AND([Content Submitted]@row=0, TODAY() > [Content Due]@row, TODAY <= WORKDAY([Content Due]@row, 4), "Yellow")), IF(AND([Content Submitted]@row=0, TODAY() >= [Content Due]@row), "Red"))

Paul Newcome

Let's start by cleaning up some of the parenthesis and redundant criteria sets. Let me know how this works for you...

=IF(OR([Content Submitted]@row = 1, TODAY() < WORKDAY([Content Due]@row, 4)), "Green", IF(TODAY() >= [Content Due]@row, "Red", "Yellow"))

Simi

Thanks Paul, but this formula does not include the check box criteria which needs to be included.

Paul Newcome

It does include the checkbox in the OR statement. If it makes it past the OR statement then the box must not be checked, so we do not need to include that criteria in any other portion of the formula.