Due Date Refresh Automation

Shannon W

Hi There,

I am wondering if the below function is possible for Smartsheet automation, and if so, how to set it up.

I have a due date of the 22nd each month and require the cell to refresh to the following due date once the current has passed. For example, if the current due date is 22/08/21, I need the cell to update to the 22/09/21 on the 23/08/21.

Any help here would be greatly appreciated.

Many thanks,

Shannon

Andrée Starå

Hi @Shannon W

I hope you're well and safe!

Yes, It's possible.

I recently developed an "advanced" recurring task list solution for a client.

I'll get back to the post with a short description of how to make it work.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Shannon W

Hi @Andrée Starå!

Thanks so much for your reply - that would be so helpful!!

I look forward to hearing more :-)

Shannon

Shannon W

Hi @Andrée Starå ,

Just following up on the above - would love to give you solution a go.

Thanks in advance!

Shannon

Kimberly Loveless

Hi @Shannon W I have actually build a reoccurring task list for my organization that works similarly to what you described above. the difference is that mine is set to record the date completed and then go the 30 days (or other if another timeframe is selected) from that completed date.

Using an automation it will also moved completed rows to another sheet so on the task list you only see the up coming tasks. This other sheet is then referenced in the "Last Review" column, where it pulls the completed date from the most recently completed time for that task.

I even have a report that pulls the tasks due that day that can be utilized.

I would be happy to give you the exact formulas used for these sheets if you are interested. 😁

(of note some of the columns in both sheets are helper columns for automations and other formulas so they may not all be needed)

Shannon W

Hi @Kimberly Loveless,

Thanks so very much for getting in touch here! So appreciated!

I would love to see the formulas if you are happy to share :-)

I have built a helper column that calculates the upcoming due date ('Upcoming Due' column), but am actually not sure if that is even helpful here.

Once the new due date has been recorded I will also need the 'Report Update' and 'Status RAG Tracker' cells to refresh as blank as well. I know I can set up and automation for this, but just trying to figure out the trigger here. I feel like a bit of a ninny here, wracking my brain.

Any help is so appreciated!

Kimberly Loveless

Hi @Shannon W, sorry in advance because this will be a lengthy response

On the Reoccurring Task List Sheet...

The way I have it set is in the my Last Review Column the formula is = MAX(COLLECT({date}, {Combined},Combined@row)) this pulls in the date of the most recently completed entry from the "Completed Task List" Sheet using the Combined Column to do so. For this you could use the Action Measures I would assume rather than a combined column

In my Next Review Column I have a long formula based on the occurrence frequency noted, you are just using 30 days so it would water down to something like =[Last Review]@row+30 that would be the next due date

In my sheet the trigger is for the automation, I met with Smartsheet Support multiple times and couldn't seem to get my change cell value automation to run consistently without it. All that is is =Modified@row to pull the date that the row was last updated, this then is used to uncheck complete and blank out the notes section on my sheets.

The Automations are...

Copy rows marked completed to the Completed Task List sheet

Alerting of tasks that are past due or due that day with a link to the report for that specific person

Clearing the Notes Column (Text/Number Column Type) -- this is what you would use for the clearing the cells you mentioned in your sheet just maybe with a different set of conditions. Per Smartsheet Support this trigger is a little more reliable than basing it off of a change, particularly in a sheet with cross sheet references.

Uncheck the Completed Column (so items show on the report when they are due again)

*** Of note the uncheck complete was set up before the clear cell value automation was released and after multiple meetings with Smartsheet Support to get this one to work I didn't was to mess with it but this automation could be in the same one as the previous one*

In the Completed Task List sheet...

The date column is simply =[Completed Date]@row this is because when pulling it back to the other sheet and in additional formulas it was not working properly

The Most Recent (checkbox column) has the formula =IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], Combined:Combined, Combined@row)), 1) this is one that I actually found here on the Smartsheet Community and all it does is check the box when it is the most recent entry for the Combined column value so again for your sheet you could use the Action Measures column

Row ID is an Auto-Generated column type

Row # is =MATCH([Row ID]@row, [Row ID]:[Row ID], 0) this is used in the Most Recent column calculation and like I said I found that solution in the community so I don't fully understand it but it works.

I hope that this all helps 😁

Shannon W

hi @Kimberly Loveless ,

Thank you SO much for sharing this and for such a thorough response, I really appreciate your time here.

I'm going it give it a go and see how it will work for us. Yay! Thanks again!!!