Help with date formula/automation

Nicole Tellez

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?  

Andrée Starå

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

Paul Newcome

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.

Nicole Tellez

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?

Scott Tewel

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.

Paul Newcome

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?

Nicole Tellez

Thanks Scott.  I scheduled some time for us tomorrow.  I appreciate the help.