Automate Flagging at risk task based on approaching due date

Hi Community,

I am looking to automate flagging a task if it comes within two weeks of one of its two due dates (each task has a recommendation date and approval date) and its currently not complete (of which there are two opportunities for it to be complete, the recommendation and the approval). The task must have a complete status twice to not be flagged. I am using the below formula but have incorrect argument set, and I'm not sure where I am going wrong.

=IF(AND([Finalize Recommendation]@row >= TODAY(-13), [Validated]@row < 1), OR([Approval Due Date]@row >= TODAY(-13), [Approval Status]@row < 1), 1, 0)


Please help :)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The reason for the error is a misplaced parenthesis. You closed the AND function before starting the OR.


    Are you able to provide a quick screenshot for reference?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Janelle Chard
    Janelle Chard ✭✭
    edited 05/26/22

    Hi Paul

    See screenshot below.


    I'm not sure the previous description was as succinct as it could be so see below for what I am trying to achieve:

    Flag if= Finalize Recommendation or Approve Due Date is less than two weeks in the future and are any status other than complete in the related status column. Finalized Recommendation status is tracked in the Validated column, and Approval Due Date is tracked in the Approval Status column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Let me make sure I understand what you need...


    If the Validated is not "Complete" and the [Finalize Recommendation] is in the previous 13 days


    or


    If the [Approval Status] is not "Complete" and the [Approval Due Date] is in the previous 13 days


    then we want to flag the row?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Paul, slight change- not previous, if it is within 13 days in the future.


    If the Validated is not "Complete" and the [Finalize Recommendation] is in the next 13 days


    or


    If the [Approval Status] is not "Complete" and the [Approval Due Date] is in the next 13 days


    then we want to flag the row?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. No worries. When thinking of dates, try to think of them as numbers. Past dates are negatives, future dates are positives, and TODAY() is zero. That means

    >= TODAY(-13)

    is the same as saying greater than or equal to 13 days in the past.


    Give this formula a whirl...


    =IF(OR(AND(Validated@row <> "Complete", [Finalize Recommendation]@row <= TODAY(13)), AND([Approval Status]@row <> "Complete", [Approval Due Date]@row <= TODAY(13))), 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!