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

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!

Screen Shot 2018-01-12 at 12.30.59 PM.png

Find more posts tagged with

Formulas

Comments

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! ?

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…