Returning Open or Closed based on conditions


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”.


-  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"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!