Status for At Risk tasks due in 7 days

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!

Find more posts tagged with

Sheets

Formulas

Solution Center

Project Management

Comments

Mike Wilday

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

Mike Wilday

An Explanation:

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

brenttopa

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,

Mike Wilday

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

Paul Newcome

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.

Mike Wilday

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.

Mike Wilday

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

Paul Newcome

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!

Mike Wilday

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

Paul Newcome

brenttopa

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.

Paul Newcome

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

Mike Wilday

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

brenttopa

That did it! Thank you!

Paul Newcome

Excellent! Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])