Flag if date created > 48 hours and completed column is blank
Hello,
I'm new to using SmartSheets and first time poster here. Hoping you can help me with an elegant solve? I'm trying to create a function where a row will be highlighted when it satisfies two criterias:
1. if the date created has been more than 48 hours
2. and completed is blank (not marked with a date)
Basically in terms of workflow automation, when a request is submitted and the other team has an SLA of 48 hours and hasn't completed the request yet, the row will be flagged/highlighted.
Thank you!!
Comments
-
Hi,
Try this.
=IF(AND(DateCreated@row <= TODAY(-2); NOT(Completed@row = "")); 1; 0)
The same version but with the below changes for your and others convenience.
=IF(AND(DateCreated@row <= TODAY(-2), NOT(Completed@row = "")), 1, 0)
Country Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
You'll want to create a Column using Symbols Flag type and put Andree's formula in that column.
-
Mike,
Thanks!
And you'd then use the flag to trigger a conditional formatting rule to highlight the row.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
If the sheet is not regularly active though, the TODAY() function won't update. I personally would go with a helper Date column and use
=[Created Date]@row + 2
I would then set up my reminder to go out on the date listed in the helper column if the Completed Date field is blank.
-
Genius idea in case users don't regularly open the sheet.
-
Thanks!
The way the post is written, it almost sounds as if new entries may be added via form.
If that is the case, a new form entry would update the TODAY() function without the sheet having to be opened.
The problem with that is if a new form isn't submitted everyday, then dates will fall behind until a new one is added again.
That's why I have gotten into the habit of using my above solution for "ticket submission" type setups.
-
I need to flag a field after 24 actual hours, not 1 calendar date in the future. For example, if a form is submitted 05/01/20 8:04AM, I need to flag a field if it hasn't been modified by 05/02/20 8:04AM. if there any way to do this in smart sheet?
I have this formula with the following logic, but it if a form is submitted 11:59 PM it will show as flagged 12:01 AM.
=IF(AND(TODAY() > [Due Date]3, ([IT Done?]3 = 0)), 1, 0)
[Due Date] is =[Submission Date]3 + 1
[Submission Date] is a date is the system submission time + 1 day
Any help would be greatly appreciated
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!