Nested IF formulas for status

Zulma PH · 02/08/22

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,

Thanks!!

Heather Duff · 02/08/22

Hi @Zulma PH ,

I think I see the problem. Your "delayed" portion of the formula is currently checking for equal to 0.01.

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

The row you have indicated that should be displaying Delayed is showing Not Started because it is not equal to 1%. Perhaps you want to change that portion of the formula to reflect >=0.01

Let me know if that works for you!

Best,

Heather

Zulma PH · 02/08/22

https://community.smartsheet.com/discussion/comment/318953#Comment_318953

Thank you, but that part of the formula is actually what I need to establish to comply with the second part of this statement:

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

Heather Duff · 02/08/22

@Zulma PH , could you possibly use this, then?

=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 <1, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))

That way it's indicating that it is less than 100% done, but doesn't necessarily have to be equal to 1%.

Zulma PH · 02/08/22

https://community.smartsheet.com/discussion/comment/319017#Comment_319017

Thank you for your support Heather, that didn't quite do the trick, because the percentage did have to be 1% for that rule to apply, how ever it made me wonder if the priority of my rules was the problem and I could solve it with this formula using an OR function for the Delayed part :

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

Heather Duff · 02/08/22

@Zulma PH Great thinking! Did that work for you?

Zulma PH · 02/08/22

https://community.smartsheet.com/discussion/comment/319022#Comment_319022

Yes! I tried it with all my projects now and haven't had any issues

Thank you!

Heather Duff · 02/08/22

Fantastic! Glad you found something that worked.

Nested IF formulas for status

Answers

Help Article Resources

Categories