At Risk Flag Automation

Jonna Critchley
Jonna Critchley ✭✭✭
in Formulas and Functions

Hi There!

Need assistance with a formula.

What I have so far is:

=IF(OR([Agreed Upon Complete Date]@row < TODAY()), IF(AND([Agreed Upon Complete Date]@row <> ""), IF(AND(Status@row <> "Complete"), 1, 0)))

This is working great for if the "Agreed Upon Complete Date" is in the past and the status is not complete, turn the flag red. In addition, this formula will not return a red flag if the Agreed Upon Complete Date is blank.

What I need to ADD to this formula is:

If the "Agreed Upon Complete Date" is in the next 3 days and the "% Complete" is less than 75% turn the flag red.

Any assistance would be appreciated!

· Share on FacebookShare on Twitter

Best Answer

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    Answer ✓

    Hello @Jonna Critchley

    Something like this perhaps?

    =IF(OR([Agreed Upon Complete Date]@row < TODAY(), AND([Agreed Upon Complete Date]@row >= TODAY(), [Agreed Upon Complete Date]@row <= TODAY() + 3, [% Complete]@row < 75)), IF(AND([Agreed Upon Complete Date]@row <> "", Status@row <> "Complete"), 1, 0), 0)

    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!