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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…