Automate Flagging at risk task based on approaching due date

Janelle Chard

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 :)

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?

Janelle Chard

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

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?

Janelle Chard

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

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)