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

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?