Recurring tasks on a fixed date
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 :)
Best Answers

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. )

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.
Answers

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!

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?

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 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.

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.

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

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!