# Hi, im trying to automate flagged risks within projects I am managing however am struggling

Options
edited 04/11/23

I have made many attempts however it confuses me. I originally wanted to have the formula to flag any tasks that were due within the week

or to flag tasks that will not be accomplished by their due date (this would be ideal however I am not sure my sheet would be equipped for this as my duration is for each task is automatically calculated.

So far I have created this =IF([End Date]1 < TODAY(+7), 1). This was to test and try a basic formula however this flags the task however even if it is okay. Any help would be greatly appreciated. I've attached a screenshot.

• ✭✭✭✭✭✭
Options

Are you able to outline the desired logic/function in detail?

• Options

Hi Paul.

Thanks for the response. I would like the sheet/reports to Flag the task at risk (using the icon) IF the End Date is 1 week away and % Complete is below 50%?

Does that make sense? Im trying to make something as close as possible.

Thanks!

• ✭✭✭✭✭✭
Options

Try this:

=IF(AND([End Date]@row<= TODAY(7), [% Complete]@row< 0.50), 1)

• Options

Hi Paul ,

Thank you! it does works perfectly, I just need to try and add in logic/ a rule to not flag the task if it is complete.

• Options

I think I got it, =IF([% Complete]@row = 1, 0, IF(AND([End Date]@row <= TODAY(7), [% Complete]@row < 0.5), 1)). It seems to work, does this look correct?

• ✭✭✭✭✭✭
Options

My formula shouldn't have been flagging rows that are more than 50% regardless of the [End Date], so it shouldn't have been flagging any rows that were complete or 100%.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!