Help with date formula/automation
I am trying to help a user automate a current process in Smartsheet. The information within the sheet is compliance info and each item has different regulatory rules. Some items have monthly update requirements, while others have quarterly or annual update requirements. Right now the sheet admin goes in the sheet to see when items were last updated and changes the due date to the next month, or whenever it's due. There is also a helper column that states the frequency of which updates are due. This sheet has 250+ lines of compliance items.
I was wondering if anyone new of a way to make this less complicated and create some sort of automation/formula to update the due dates based on the frequency column?
Comments
-
Hi Nicole,
I'd suggest setting up Automation Workflow(s) with recurrence. Might work with one with conditions otherwise one per recurrence length.
Another option could be to use the third-party service, Zapier to automate the change of the due date.
Would any of those options work?
Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
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 you do not need to track historical data, you could use a field for users to manually input the completion date, then use a table along with an INDEX/MATCH to add the appropriate amount of time based on your helper column that establishes the frequency.
If Andree's solutions don't work for you, but this one does, let me know and I'll be happy to provide more details.
-
Thanks Paul, this is exactly what I was thinking might be a solution, but I wasn't sure how to go about it. Can you elaborate a bit more?
-
Hi Nicole,
I'm the product manager for automation at Smartsheet. I'd love to spend a few minutes learning more about this scenario. It is also likely that there is existing functionality which can help as Andree and Paul mentioned (thanks both). If you could spare 15-20 minutes to discuss it would really be helpful as we consider the roadmap. Please use this link to set up time. Thanks.
-
My apologies for the delay. I didn't get a notification that you had responded.
Are you able to provide some screenshots with sensitive/confidential information removed, blocked, or replaced with "dummy data"?
After looking over the post again, depending on how many different frequencies you have, we may be able to set up a group of Automation Workflows (one for each frequency). The biggest determining factor is whether or not you need to just trigger a notification or if the next due date needs to be displayed.
Another determining factor would be whether or not you need to track historical data. How important is it that you track the previous completion dates? Do they need to be displayed? Can you just use cell history to view as needed? Is it not important at all?
-
Thanks Scott. I scheduled some time for us tomorrow. I appreciate the help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!