# At Risk Flag - Past Due

edited 12/09/19

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
edited 04/18/18

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

• ✭✭✭✭✭✭

I'm afraid I did! Thanks, Chris.

• ✭✭✭✭✭✭
• 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

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭

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

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!