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.

Past Due Flag.JPG

Find more posts tagged with

Construction

Formulas

Project Management

Comments

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:

If [End Date] is less than today AND
[End Date] is a date (i.e. not blank) AND
[Item Complete] is unchecked AND
Either [% Complete] is less than 1 (i.e. 100%) OR [Item Complete] is unchecked THEN
Flag the cell the formula resides in as 1 (i.e. checked) ELSE
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

IS Date.jpg

Smartsheet formula working.jpg

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…