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
-
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?
-
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.
-
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?
-
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?
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!