Nested IF formulas for status


I am trying to write a nested IF formula, but as I run it there is one argument that is not resulting as I expected.

Basically what I want my Status column to represent is the following

-Done: when a project is in 100% compliance

-In Progress: when a project is anywhere from 1%-75% and its within the due date (the compliance % column is a Single select-Drowpdown list with options 1%, 25%, 50%, 75%, 100%)

-Not started: when a project is in 1% compliance but within the due date

-Delayed: when a project is not finished and the due date has expired OR when a project hasn't been started and today (as in any given day the report is consulted) is in less than 10 days from the due date.

I added the ¨Expected Result¨ column for a better appreciation of where I'm trying to get at

This is the nested formula that I am using:

=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row = 0.01, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))

I hope I was sufficiently clear and that you can help me,




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!