At Risk Flag Formula Help

mmac

Hi Paul,

 

Thank you for the support. Extremely helpful.

All scenarios are now in working order for the exception of the below.

Wouldn't want to have at risk flagged if the due date is further out than 3 days from today's date.

For example, if the due date is on 10/31, it's not at risk until it's 3 days from whatever that day's date would be at the time, or if it's past due then it obviously would be flagged.

Please see example:

https://www.loom.com/share/5eb593313b8d4e89b9a781c6bf29ef1b

 

Again, it checks off on all scenarios from the exception of what was mentioned above. Appreciate the further help!

Paul Newcome

No worries.

 

It has to do with this portion here

 

...................IF(AND([Baseline Due Date]@row > Today(), NOT(ISDATE([Date Received]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1..........................

.

More specifically the Greater Than operator.

 

I am not sure where this section of the formula came from, but this is what is causing that flag.

 

Based on my previous suggestions and removing that section of the formula, here is what you would end up with. Dragfill this on down the entire column (parent and child rows alike) and give it a test then let me know how it works out for you.

 

=IF(COUNT(CHILDREN([Task Name Column]@row) > 0, IF(COUNTIFS(CHILDREN(), 1) > 0, 1), IF(ISDATE([Baseline Due Date]@row), IF(AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1)))

mmac

Hi Paul,

 

I think we're almost there. I hit an error when adding.

I've published out the test sheet and it can be accessed and edited here:

https://app.smartsheet.com/b/publish?EQBCT=88ef27b612be4f1f8083d012919829d5

 

I highlighted where I placed the latest formula in Yellow.

 

Appreciate the help!

Paul Newcome

It was a simple matter of a missing closing parenthesis. I added some notes to a few cells to show where and what I did.

 

Let me know if it works for you.

Mike Wilday

It. 

mmac

Thank you kindly, you've been a huge help! I very much appreciate it. Everything worked as expected.

mmac

Thank you again Mike for the support!

Paul Newcome

Happy to help! 

Mike Wilday

Hey, I'm glad I could help you out. 

Fletcher Boll

I have a similar issue. I have a health column that reads RGY symbols from my completed column and that is working fine. But I'd like to set the parent row in health to notify me if any of the children below are red (delayed).

I've been trying variations of:

=IF(AND(CHILDREN([completed12:completed44= "delayed", "Red")) (#unparsable)

=IF(COUNT(CHILDREN([completed12:completed44= "delayed", "Red")))

Not sure what to do...

Thank you for your help!

Screen Shot 2020-02-06 at 11.18.16 AM.png

Paul Newcome

@Fletcher Boll Try something like this...

=IF(CONTAINS(CHILDREN(), "Red"), "Red", IF(CONTAINS(CHILDREN(), "Yellow"), "Yellow", "Green"))

Fletcher Boll

THANK YOU!

Paul Newcome

@Fletcher Boll Happy to help! 👍️