Returning Open or Closed based on conditions

t.siddall101151

Referring to Row 6

I have a Status column and that can be Open or Closed.  It depends on the condition of the Progress column which can be selected from the following dropdown menu.  The first 4 conditions mean the Action Item is “Open”.  The last condition means the Action Item is “Closed”.

Dropdown:

-  Not yet submitted

-  Submitted for Review

-  Submitted for Approval

-  Returned to Action Owner

-  Approved by Approver

 

So, I want the Status column to return “Open”, or “Closed” based on the 5 conditions.

Additionally if the Date in the Due Date column has passed I want the return to be “Overdue” rather than “Open”.  Overdue is a sub set of “Open”

I tried the following:

=IF(OR(Progress6 = "Not yet submitted", Progress6 = "Submitted for Review", Progress6 = "Submitted for Approval", Progress6 = "Returned to Action Owner"), "Open", “Closed”, IF([Due Date]6 < TODAY(), "Overdue"))

Paul Newcome

Try something like this...

 

=IF(Progress@row = "Approved by Approver", "Closed", IF([Due Date]@row < TODAY(), "Overdue", "Open"))