At Risk triggered by formula

Hello!! I'm trying to target the "At Risk" column with a formula to "flag" automatically.

I need to have my At Risk flag marked automatically if a task is overdue [End Date] and the [Status] is "Not Started" and "In Progress"

I tried using this formula:

=IF(AND([End Date]@row <= TODAY(), Status@row <> "Not Started" "In Progress"), 1, 0)

I'm getting an #UNPARSEABLE error

Please help,

Thanks!


Best Answers

Answers

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Answer ✓

    Hello @Estefania Reyes

    Please try this:

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

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Estefania Reyes,

    If I've understood correctly, this should be your formula:

    =IF(AND([End Date]@row <= TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0)

    If Status is either "Not Started" or "In Progress" and the End Date is equal to or before today, then the box is ticked.

    If I've misunderstood some part of this, let me know and I will correct it. If you've any other questions etc. on this, then just post! 😊

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓

    Try:

    =IF(AND(End@row <= TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0)

    You've almost got it, just need to throw in the OR function to catch both cases of the status.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Estefania Reyes
    Estefania Reyes ✭✭✭
    Answer ✓

    Thank you! It worked. :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!