Help with Complicated Formula IF(AND(OR involving % Complete and RYGG balls

Kimberley Smith

Hello there!  I'm a relatively new Smartsheet user and I've got an issue with a formula that I could really use your help with.  Here's the formula:

IF(AND(OR([Initial Draft Due or Task Start]12 < TODAY(), [Requested Edits Due]12 < TODAY(), [Revisions Due]12 < TODAY(), [Final Approval Due]12 < TODAY()), [% Complete]12 < 1), "Red", IF(AND(OR([Initial Draft Due or Task Start]12 >= TODAY(), [Requested Edits Due]12 >= TODAY(), [Revisions Due]12 >= TODAY(), [Final Approval Due]12 >= TODAY()), [% Complete]12 < 1), "Yellow", IF([% Complete]12 = 1, "Green", IF(AND([Initial Draft Due or Task Start]11 = "", [Requested Edits Due]11 = "", [Revisions Due]11 = "", [Final Approval Due]11 = ""), "Gray", ""))))

Here is what I'm trying to accomplish:  A task has 4 possible date columns.  If a task hasn't been started yet, there will be no dates entered at all, and % Complete should be 0% (gray).  Once a task starts, the Initial Draft Due or Task Start date will be entered and person assigned may or may not adjust % Complete (yellow). Typically, after the draft is submitted, the next date is entered, and so on until % Complete is 100% (green).  If one of those dates is past due, and the task isn't 100%, then it should be red. I am trying to automate update requests for past due tasks.  

I hope that all makes sense, but maybe I'm over-complicating it and there's an easier way.  I'd really appreciate any assistance you can give.  I've attached a snip of relevant columns and some example data.  Thanks!  

Shaine Greenwood

Hi Kimberley,

From what I can see, your formula is correct and appears to be the most efficient way to do what you need.

You said that you've got an issue with the formula...would you mind letting me know the behavior/issue that you're experiencing?

Robert S.

Hello Kimberley,

 

Thanks for the question. In looking over your formula I can see a few spots that would cause a different result than what it seems like you're looking for from your scenarios. Here's what I came up with that seems to fit the results you're looking for:

 

=IF(AND(ISBLANK([Initial Draft Due or Task Start]@row), ISBLANK([Requested Edits Due]@row), ISBLANK([Revisions Due]@row), ISBLANK([Final Approval Due]@row)), "Gray", IF([% Complete]@row = 1, "Green", IF(AND(OR(AND([Initial Draft Due or Task Start]@row < TODAY(), NOT(ISBLANK([Initial Draft Due or Task Start]@row))), AND([Requested Edits Due]@row < TODAY(), NOT(ISBLANK([Requested Edits Due]@row))), AND([Revisions Due]@row < TODAY(), NOT(ISBLANK([Revisions Due]@row))), AND([Final Approval Due]@row < TODAY(), NOT(ISBLANK([Final Approval Due]@row)))), [% Complete]@row < 1), "Red", "Yellow")))

 

With nested IF formulas such as this, the order matters greatly. The formula will work from left to right, and stop once it finds a true statement. More on nested IF formulas can be found here (https://www.smartsheet.com/blog/support-tip-build-nested-IF). Another thing to note, is that blank date cells are technically seen as less than another date, including the TODAY function. The formula above is also utilizing the @row feature, to help with performance and efficiency as your sheet grows. For more on @row, check out this help center article (https://help.smartsheet.com/articles/2476491#row).

Kimberley Smith

Thank you so much for responding.  If you look at the first red ball in the pic I shared, you’ll see that the due date was in the future so it should be yellow.  

Kimberley Smith

Thank you so much, Robert, for your help.  I will try the improved formula and let you know how it goes! ?