Best practice / ideas for dealing with recurring task reminders

TomW

Hi,

I'm creating a sheet which will contain a list of about 200 tasks.

These tasks are all recurring, although not with the same frequencies. Some need to take place annually, some are quarterly, some monthly (and not always on the same day of the month).

I need to create some way to track whether tasks have been completed or if they are still outstanding. The ultimate goal is to have notifications for when tasks are due, and have a dashboard to show recently completed tasks and tasks falling due soon.

I want to somehow automate the fact that the tasks are recurring as I don't want to have to copy down new rows for new tasks. Even for tasks that are quarterly or monthly I'm hoping these can just be one row item in the sheet rather then multiple for each month/quarter...

I'm not 100% sure what the best way to do this is, but was thinking something along the lines of:

1. Have two sheets, one with task reminders and one to keep track of completed tasks.
2. In the Task Reminders sheet have a Due Date automatically calculated based on an Initial Reference Date column and a Frequency column. This may be complex due to the different dates and frequencies but should be possible.
3. When a task is marked as complete copy the row to the Completed Tasks sheet, and automatically recalculate the next due date - although I'm not 100% sure how to do this.

Does anyone have any experience of doing something similar? Any better ways of doing this?

Ryan Sides

Hey Tom, that's a complex problem, but I'd approach it like this..

Sheet 1 Your Main Sheet that kicks off automated reminders. This is where your folks mark the completed date.

When an row is marked completed, it gets moved to sheet 2

Sheet 2 Your Formula sheet that determines the frequency of the next due date and replaces the current due date on the row with the new due date (This formula would need to reference a 4th sheet to determine the next due date based on the type of row). When your row hits this sheet it picks up the new due date and moves to Sheet 3. (You may need to automate the row move every hour or so; that way, smartsheet has a delay in populating the info from sheet 4 in case it doesn't get to it before it moves the row to sheet 3)

Sheet 3 is your history sheet. When a row hits here it gets copied to Sheet 1, replacing the original that just left that sheet. Only now, it has its new due date. And you retain a record of it on sheet 3.

Whew! Would take some brain power, but could be done.

TomW

Thanks for the reply Ryan. That would have been a great solution and would have worked really well but in the end I opted for a simpler approach which will be easier for my client to maintain when I've moved on. (i'm worried about leaving them with a solution that is hard to unpick if something breaks when I've gone).

I ended up manually copying down quarterly and monthly tasks so that there was a row for each task due in the year. I added the due dates for 2023, which is used as an initial 'Reference Date'. I made it so the sheet can easily be copied and rolled forwards for future years by using the DATE formula to create a 'this years due date' using the DAY and MONTH formulas to pull the day of month and month of year from the Reference Date column and a Sheet Summary value for the YEAR. This makes it really easy to roll the sheet forwards for next year by changing one date in the Sheet Summary and clearing the status and completed date columns.

I put the sheet, reports and dashboard in a folder so the whole folder can be copied for next year without having to update any reports of dashboards.

It would be great if Smartsheet had better functionality for automatically recurring tasks as that would remove the need for any ongoing maintenance of the sheet.