Help with date formula/automation

Nicole Tellez
Nicole Tellez ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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

  • Andrée Starå
    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

    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.

  • Paul Newcome
    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
    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?

  • 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
    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
    Nicole Tellez ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!