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?
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!
-
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?
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!
-
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)
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!