Red flag if the END DATE is past AND not blank, and if the STATUS is not complete.

System
System Employee Admin
edited 06/23/22 in Formulas and Functions
This discussion was created from comments split from: Flag task as late where task incomplete and past due date.

Answers

  • This formula gets me closer to what I need to do. Here's my version of the working formula above that "works" but isn't doing what I want.

    =IF(AND(Status@row <> "Complete", [End Date]@row <= TODAY()), 1)

    What I need is the following in English:

    Only Raise a red flag if the END DATE is in the past, if the END DATE is not blank, and if the STATUS is not complete.

    Your assistance is much appreciated. I'm not great with these advanced formulas.

  • Dan W
    Dan W ✭✭✭✭✭
    edited 06/23/22

    =IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete", [End Date]@row <> ""), "RED", "")

    You can change the "Red" to whatever you need. If its a check box you can simply change it to ""), 1,"")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!