At Risk Flag - Past Due

Nicole Stewart

I want to use the "At Risk" Flag to populate when an item is not marked complete and is past due.

I assume I need to use an AND or and OR formula - but this isn't working.  The formula below yields no populated flags.

 

The line for "Tag Drawings" should populate a flag because it is past due and is not completed.

Mike Wilday

Can you post your formula attempt so that we can troubleshoot what's wrong? 

Chris McKay

Hi Nicole,

I think Mike may have missed the formula included in your screenshot .

You only need an AND operator in your formula as there are no OR criteria you need to evaluate based on your requirements. OR / AND can be nested inside each other when you have a decision tree (i.e. multiple paths) that can be taken.

Based on your example, this might be a typical example of using both operators:

=IF(AND([End Date]1 < TODAY(), ISDATE([End Date]1, OR([% Complete]1 <1, [Item Complete]1 = 0), 1, 0)

This is essentially saying:

1. If [End Date] is less than today AND
2. [End Date] is a date (i.e. not blank) AND
3. [Item Complete] is unchecked AND
4. Either [% Complete] is less than 1 (i.e. 100%) OR [Item Complete] is unchecked THEN
5. Flag the cell the formula resides in as 1 (i.e. checked) ELSE
6. Flag the cell the formula resides in as 0 (i.e. unchecked)

If you're just wanting to address the requirements you listed, however, then this will do:

=IF(AND([End Date]1 < TODAY(), [Item Complete]1 = 0), 1, 0)

Kind regards,

Chris McKay

Mike Wilday

I'm afraid I did! Thanks, Chris. 

Chris McKay

kathy66086

Chris - I pasted your formula above tin an attempt to automate the risk flag and I keep getting this #UNPARS error.  I'm a project manager, not a programmer so this is really frustrating - I really appreciate your help.

(we paid to upgrade to get the Project Roll up capabilities that have the Risk sheet included and it should work automatically.)

 

Thanks

Kathy

Andrée Starå

Hi Kathy,

Can you share the sheet or some screenshots so that I can getter a better understanding of your sheet structure?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

AbbyT

Hello Im trying to write a formula to get the "at risk" flag to go red if the end date is greater than the customer expected delivery date.

I have managed to do this, but the flag also goes red if there is no date in the "customer expected delivery date" cell

How do i get the flag to stay blank if there is no date in the Customer expected delivery cell?

See screenshots below

Andrée Starå

Hi Abby,

Try something like this. This will not flag it red if either date cells are empty.

=IF(OR(ISBLANK([Customer Exp Delivery Date]@row); ISBLANK([Actual End Date]@row)); 0; IF([Actual End Date]@row < [Customer Exp Delivery Date]@row; 1))

The same version but with the below changes for your and others convenience.

=IF(OR(ISBLANK([Customer Exp Delivery Date]@row), ISBLANK([Actual End Date]@row)), 0, IF([Actual End Date]@row < [Customer Exp Delivery Date]@row, 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting