Recurring tasks on a fixed date

Simon One

Hello community,

I am looking for a function to create recurring tasks with a fixed day of the week or date. For example, every Monday of the week or every first Friday of the month.

The repetition after X days is not sufficient in many cases.

I hope smartsheet has a solution for this :-)

Danielle Arteaga

Hi, Simon. You might try using a calculated field in your worksheet that leverages WEEKDAY.

For example, let's say you have a column called [Completion Date]. It is a date column. Let's also assume you want the next activity due to occur on the first Monday following the completion date.

You can first calculate the weekday number for [Completion Date] by creating a helper column (call it [Weekday Helper]) using this formula: =WEEKDAY([Completion Date]@row).

This will return a number, where 1 equals Sunday, 2 equals Monday and so on.

You can then create a second column (date format) where you place a nested IF statement as follows:

=IF([Weekday Helper]@row = 1, [Completion Date]@row + 1, IF([Weekday Helper]@row = 2, [Completion Date]@row + 7, IF([Weekday Helper]@row = 3, [Completion Date]@row + 6, IF([Weekday Helper]@row = 4, [Completion Date]@row + 5, IF([Weekday Helper]@row = 5, [Completion Date]@row + 4, IF([Weekday Helper]@row = 6, [Completion Date]@row + 3, IF([Weekday Helper]@row = 7, [Completion Date]@row + 2)))))))

This will then give you the next due date that will always be a Monday, and you can build a workflow automation using this date.

You can adjust this to be any day of the week by tweaking the numbers in the formula to correspond to that day.

Here's an example (and FYI - you do not need the [Weekday Name] or [Confirm Monday] columns - I've included those just to illustrate the concept and confirm testing. )

Danielle Arteaga

@Simon One

This may not be the only / best way, but here's what I've got for you.

Task Date is a date column

Year is a text column with the following formula: =YEAR([Task Date]@row)

Month is a text column with the following formula: =MONTH([Task Date]@row)

Next Due Date is a date column with the following formula: =DATE([Year]@row, [Month]@row+1, 01)

This calculates day 1 of the month following the Task Date, and you can use this date column to create an automated workflow. You can hide the Year and Month columns on your worksheet, because they serve only a "helper" function.

ker9

Hi @Simon One

If I understand you request correctly, perhaps this will help.

Trigger your workflow when a date is reached

Select Custom in the dropdown circled

Change the recurrence to run every week on whatever day you'd like.

You can set up conditions to further manage the running of it, including telling it when to end based on a date.

Hope this helps!

Simon One

Hi @ker9 ,

thank you for your answer to my question. Unfortunately, that's not the solution I'm looking for. I'm not looking for time automation. I want to calculate the next date of a task/row according to the last completed date.

Do you also have a tip here?

Danielle Arteaga

Hi, Simon. You might try using a calculated field in your worksheet that leverages WEEKDAY.

For example, let's say you have a column called [Completion Date]. It is a date column. Let's also assume you want the next activity due to occur on the first Monday following the completion date.

You can first calculate the weekday number for [Completion Date] by creating a helper column (call it [Weekday Helper]) using this formula: =WEEKDAY([Completion Date]@row).

This will return a number, where 1 equals Sunday, 2 equals Monday and so on.

You can then create a second column (date format) where you place a nested IF statement as follows:

=IF([Weekday Helper]@row = 1, [Completion Date]@row + 1, IF([Weekday Helper]@row = 2, [Completion Date]@row + 7, IF([Weekday Helper]@row = 3, [Completion Date]@row + 6, IF([Weekday Helper]@row = 4, [Completion Date]@row + 5, IF([Weekday Helper]@row = 5, [Completion Date]@row + 4, IF([Weekday Helper]@row = 6, [Completion Date]@row + 3, IF([Weekday Helper]@row = 7, [Completion Date]@row + 2)))))))

This will then give you the next due date that will always be a Monday, and you can build a workflow automation using this date.

You can adjust this to be any day of the week by tweaking the numbers in the formula to correspond to that day.

Here's an example (and FYI - you do not need the [Weekday Name] or [Confirm Monday] columns - I've included those just to illustrate the concept and confirm testing. )

Simon One

@Danielle Arteaga this is really great. That's exactly what I was looking for. Many thanks for this skill.

Do you have also a solution for me to automatically repeat tasks on the first of every month?

I wish you a nice weekend and thank you very much.

Danielle Arteaga

@Simon One

This may not be the only / best way, but here's what I've got for you.

Task Date is a date column

Year is a text column with the following formula: =YEAR([Task Date]@row)

Month is a text column with the following formula: =MONTH([Task Date]@row)

Next Due Date is a date column with the following formula: =DATE([Year]@row, [Month]@row+1, 01)

This calculates day 1 of the month following the Task Date, and you can use this date column to create an automated workflow. You can hide the Year and Month columns on your worksheet, because they serve only a "helper" function.

Simon One

@Danielle Arteaga I was on vacation so I apologize for the late reply. You helped me again. Thanks a lot!

Jubial

This is a hassle if you are using the dependency management feature....you would need two date columns as you can't add formulas to a date if there is a dependency marked.