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?

Find more posts tagged with

Formulas

Conditional Formatting

Update Requests

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet