At Risk Flag Formula Help

Try this: =IF(AND([Baseline Due Date]@row <= TODAY(3), [Baseline Due Date]@row >= TODAY(), ISDATE([Date Received]@row, Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)

Sorry - Need to edit that:

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

Thank you! That worked for each scenario for the exception of testing if the due date is in the past. That did not seem to flag the at risk = Yes.

In addition, I am trying to flag at risk = yes for the parent row when the # of at risk tasks is greater than 0.

Currently, the # of at risk tasks counts the sum of the children rows flagged as at risk, which I wanted to keep in place. But in addition to that, I would like to flag the parent row as at risk if the value is greater than 0.

_20 WIP - Template of NEW Campaign Pr - Smartsheet com.png

Try removing the portion of

=IF(AND(........, [Baseline Due Date]@row >= TODAY(), ..........), 1, 0)

Try this one...

=IF(OR([# of At Risk Tasks]@row > 0, AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1)), 1, 0)

Try this one... It checks for both cases.

=IF(AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, IF(AND([Baseline Due Date)@row > Today(), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)

Try this one which checks for the first case, then for the second case.

https://app.smartsheet.com/b/publish?EQBCT=bdf3f97a3f6843a081a13451745a1d8c&ss_v=86.1.2

Thanks! This is helpful. It does solve for if the baseline due date is in the past to flag.

Two findings with this formula after having dragged down the at-risk column.

1) When baseline due date is blank, at risk is flagged. Ideally, it wouldn't be if the baseline due date is blank.

2) The # of at risk tasks at the parent level is no longer being counted when I added the suggested formula to the at risk cell linked to the parent row. In the # of at risk tasks at the parent row, in that cell, I currently have "=COUNTIF([At Risk]:[At Risk], 1)"

Criteria I am hoping to find in a formula is:

Parent row: sum # of at risk tasks based on children rows that are flagged as at risk. (e.g. 3)

If three tasks indented under the parent row are flagged, the sum value returned would be 3 AND the parent row would be flagged as at risk, at the project level.

For the children rows:

If baseline due date is not populated, do not flag at risk

If baseline due date is 3 days out, flag at risk IF status does not = yes and % complete is not 100

If baseline due date is populated and in the past, flag at risk IF status does not = yes and % complete is not 100

I've gone ahead and shared out the sheet for further testing/support. Thank you in advance for additional time spent in resolving this.

Thanks, Mike! I'm eager to try that, but it's not being accepted. I corrected the spelling error in Received, but still no luck.

I found it. Had a parenthesis when I needed a bracket in there... Try again... this is for the children columns.

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

I imagaine you can add something to the front of this that will check for the parent rows....

https://www.loom.com/share/cda3d613839f4baebc1e984a27d21572

Thank you, Mike!

The issues that I am still facing are the following:

When I paste the formula into the at-risk cell, and the baseline due date is blank, the at-risk flag is triggered when I wish it would not be.

If baseline due date is blank, at-risk flag should not be triggered.

Please see video example:

baseline due date = blank, no at-risk flag triggered
baseline due date is populated and due is within the next 3 days, trigger as at-risk
baseline due date is in the past and the date received is left blank, trigger as at-risk
if % complete = 100% OR status = Yes (complete), no at-risk flag triggered

You can add that in an IF statement to basically say "If the Baseline Due Date is a date, then run the formula, otherwise leave blank" like this...

=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, IF(AND([Baseline Due Date]@row > Today(), NOT(ISDATE([Date Received]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0))

You can also use this same concept to check for parent rows and run your COUNTIFS if there are children and run the above if there are not.

=IF(COUNT(CHILDREN([Task Name Column]@row) > 0, IF(COUNTIFS(CHILDREN(), 1) > 0, 1), insert above formula here)

When trying to access the published sheet, I got the below notice. To allow access, you will need to publish the sheet and adjust the setting in the second screenshot.

Paul's rendition should work. Let us know if it doesn't. @Paul I like how you trigger the IFstatement on whether there is a date there or not. I've always just added the criteria, but it makes sense. I like it.