Recurring tasks

Heather Duff · 04/12/21

Hi all,

Our director is hoping to start a Smartsheet that lists our recurring annual/semi-annual/biennial tasks, and sends reminders as each due date approaches. I don't anticipate it being difficult, but...

My "work smarter, not harder" mind wants to find a way to automate the recurrence. For example, if our annual audit is due on April 30 every year and the "done" column is checked, I'd ideally like it to add a new row for that task with next year's due date on it.

Any thoughts? Ideas from someone who already does something like this? Any ideas you have are greatly appreciated.

Thanks,

Heather

Paul Newcome · 04/14/21

I would recommend first setting up a sheet with each task listed once, then "Save as New" to duplicate it. The first sheet will be your "Working Sheet" and the second sheet will be the "Dup Sheet".

On the Dup Sheet, we are going to add a text/number column and use this formula:

=COUNTIFS({Working Sheet Task Name Column}, @cell = [Task Name]@row, {Working Sheet Done Column}, @cell = 1)

This will count how many of that particular task has been checked off as "Done".

Also on the Dup sheet we want another text/number column for you to record the frequency of the corresponding task.

In the Due Date column of the Dup Sheet, we want to use a formula to indicate the next due date based on the frequency and referencing the last inserted Due Date on the Working Sheet (MAX).

To pull the most recent due date from the Working Sheet for each task:

=MAX(COLLECT({Working Sheet Due Date Column}, {Working Sheet Task Name Column}, @cell = [Task Name]@row))

Now we can work on the formula based on the frequency. We know we need to use a DATE function so that it outputs a date.

=DATE(yyyy, mm, dd)

The day should be easy enough. We just pull the day from the MAX/COLLECT and drop it in.

=DATE(yyyy, mm, DAY(MAX(COLLECT(.......))))

The month and year though are going to be a little bit trickier. We need to output for 6 months, 12 months, or 24 months depending on your frequency.

12 and 24 months should be easy enough because we are adding either one or two years and pulling the month from the MAX/COLLECT

Year:

YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Biannual", 2))

Month:

MONTH(MAX(COLLECT(.....)))

The tricky part is going to be the semi-annual. We can accommodate moving into the next year by saying that if the month is greater than 6 then add 1 to the year and subtract 6 from the month, but if the month is less than or equal to 6 then we can leave the year alone and just add 6 to the month.

Year:

YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(.....)))> 6, Frequency@row = "Semi-Annual"), 1)))

MONTH:

MONTH(MAX(COLLECT(.....))) + IF(Frequency@row = "Semi-Annual", IF(MONTH(MAX(COLLECT(.....)))> 6, -6, 6))

Now that we have that figured out, we can drop it into our DATE function and end up with something like this...

=DATE(YEAR(MAX(COLLECT(.....))) + IF(Frequency@row = "Annual", 1, IF(Frequency@row = "Bi-Annual", 2, IF(AND(MONTH(MAX(COLLECT(.....)))> 6, Frequency@row = "Semi-Annual"), 1))), MONTH(MAX(COLLECT(.....))) + IF(Frequency@row = "Semi-Annual", IF(MONTH(MAX(COLLECT(.....)))> 6, -6, 6)), DAY(MAX(COLLECT(.......))))

Now we are taking the most recent date from the Working Sheet, adding the appropriate number of months and/or years to it, and recording it in the Due Date column of the Dup Sheet.

Last but not least, we set up a Copy Row automation on the Dup sheet that says whenever that Count column on the Dup Sheet (the one we added in the beginning) changes to any value, copy that row over to the Working Sheet. Since the Copy Row automation only copies over static data, it will capture that date from the formula and insert a new row on the Working Sheet with this static date of when it is due next. Now that the MAX date for that task has been updated on the Working Sheet, the Due Date in the Dup Sheet will automatically update and then will sit and wait until there is another box checked in the Done column on the Working Sheet for that task which will then trigger another Copy Row automation, so on and so forth.

To set up the alerts for upcoming tasks, you would set up the Automations on the Working Sheet on the Working Sheet.

Also on the working sheet... When the Copy Row automation triggers, it will automatically copy over the row with ALL columns from the Dup Sheet. This includes the Count column. You can either leave this visible on the Working Sheet to show how many times that particular task has been completed, or you can hide it for cleanliness. That is up to you.

Paul Newcome · 04/14/21