# Status for At Risk tasks due in 7 days

Options
edited 12/09/19

I have 4 status categories for my project with respective RYGB balls:

• Complete (blue)
• At Risk (red)
• Requires Attention (yellow)
• On Track (green)

My current formula in the Health column automatically changes the RYGB balls when the status is changed manually:

=IF(Status2 = "On Track", "Green", IF(Status2 = "Complete", "Blue", IF(Status2 = "Requires Attention", "Yellow", IF(Status2 = "At Risk", "Red"))))

However, I would like to incorporate some automation in the Status column:

1) Status becomes "At Risk" when % Complete is less than 100% and Finish Date is within 7 days

2) Status becomes "On Track" with any other % Complete status less than 100% as long as Finish Date is outside 7 days

The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation.

I have created this formula based on my research but it is coming back as unparseable:

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))

Any help would be greatly appreciated!

## Comments

• ✭✭✭✭✭✭
Options

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))

The bold and underlined part of your formula is causing that issue.

Try this modification:

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 >= Today(7), [% Complete]1 < 1), "At Risk", "On Track")))

• ✭✭✭✭✭✭
Options

An Explanation:

1. The "On Track" that appears after the "", is creating a non argument. I removed it because you added to the end of the third if statement.
2. And statements need to be comma-separated right after the IF like this. =IF(AND(This, That), "THEN DO THIS"

That should do the trick for you.

• Options

Thanks so much Mike!  Your formula worked correctly once I changed ">= Today(7)" to "<= Today(7)" to find tasks due in the next 7 days.  My mistake for not being clear on the requirement.  Appreciate your help,

• ✭✭✭✭✭✭
Options

Awesome. I am so glad I could help you out.

• ✭✭✭✭✭✭
Options

Just a heads up...

"The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation."

Once the status is manually changed, it essentially deletes the formula from the cell. Any updates made after that would have to be done manually unless the formula is re-entered back into the cell after the manual change.

• ✭✭✭✭✭✭
Options

Yes, Paul is accurate in what he is saying. It is my recommendation to automate EVERY status in the status dropdown and to lock the column because any manual changes will overwrite formulas in the cell for good.

• ✭✭✭✭✭✭
Options

You might consider adding a checkbox column for "Requires Attention" and then automating the status based on the checkbox.

• ✭✭✭✭✭✭
Options

Thanks, Mike. I guess a recommended solution would be a good idea when pointing out an issue instead of just saying "this is a problem". Haha. That's one of my "pet peeves" and I just did it. Oops!

• ✭✭✭✭✭✭
Options

@Paul - no worries. I'm glad you caught that. That was an important breakdown in the process.

• ✭✭✭✭✭✭
Options
• Options

Hi Mike,

That's a great point.  I didn't realize that the formula gets deleted when the status is manually changed.  I've set up a new checkbox column titled "Requires Attention" and added to my formula in the "Status" column (underlined section below is the added portion):

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 <= TODAY(7), [% Complete]1 < 1), "At Risk", "On Track", IF([Requires Attention]1 = 1, "Requires Attention", "On Track"))))

However, I'm getting an INCOMPLETE ARGUMENT SET error.  Checking the box under "Requires Attention" does nothing.

• ✭✭✭✭✭✭
Options

Give this a whirl and see how it does for you...

=IF([Requires Attention]@row= 1, "Requires Attention", IF([% Complete]@row= 1, "Complete", IF([% Complete]@row= "", "", IF(AND(Finish@row <= TODAY(7), [% Complete]@row< 1), "At Risk", "On Track"))))

• ✭✭✭✭✭✭
Options

Try this one... There was an extra "on Track" after "At Risk".

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 <= TODAY(7), [% Complete]1 < 1), "At Risk", IF([Requires Attention]1 = 1, "Requires Attention", "On Track"))))

• Options

That did it!  Thank you!

• ✭✭✭✭✭✭
Options

Excellent! Happy to help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!