I am trying to create Workflows based on varying frequency and day row is added.
I have a Smartsheet that gets updated with new data every Monday. As soon as a row is added, the project manager gets a notification to set the "Reporting Frequency". This can be Weekly, Bi-Weekly, or Monthly.
The first Thursday after the row is added, a Buyer should receive a workflow that requests updates. Then the Buyer should get the next request for update based on the Reporting Frequency. If Reporting Frequency = Weekly, the next request for update should be the following Thursday. If Reporting Frequency = Bi-Weekly, the next request for update should be in two Thursdays. If Reporting Frequency = Monthly, the next request for update should be in 4 thursdays. These updates can run for many cycles, so creating a new column to capture each update is not realistic.
If workflows allowed to start a reoccurrence starting with a date field in the Smartsheet, i think I would be all set. But unfortunately I don't see that option. I am really struggling with any sort of formula to tie the workflow to.
Any help would be greatly appreciated!
Best Answer
-
If it is starting on the next Thursday, Then you will want a column that has the "kick-off" date which will be used for the first automated message and use this formula:
=[Row Created]@row + ((5 - MOD(WEEKDAY([Row Created]@row), 5)) + IF(WEEKDAY([Row Created]@row) >= 5, 2))
Then a second column that will automatically update to the next appropriate Thursday based on the frequency:
=[Row Created]@row + (((INT((TODAY() - [Row Created]@row) / IF([Reporting Frequency]@row = "Weekly", 7, IF([Reporting Frequency]@row = "Bi-Weekly", 14, 28)))) + IF(WEEKDAY(TODAY()) >= 5, 1)) * IF([Reporting Frequency]@row = "Weekly", 7, IF([Reporting Frequency]@row = "Bi-Weekly", 14, 28))) + 1
Answers
-
What if the row is added on a Thursday. I assume you would want the first one to go out next Thursday or would you want it to go out "today"?
-
Hey there! New rows will only be added on Monday.
-
If it is starting on the next Thursday, Then you will want a column that has the "kick-off" date which will be used for the first automated message and use this formula:
=[Row Created]@row + ((5 - MOD(WEEKDAY([Row Created]@row), 5)) + IF(WEEKDAY([Row Created]@row) >= 5, 2))
Then a second column that will automatically update to the next appropriate Thursday based on the frequency:
=[Row Created]@row + (((INT((TODAY() - [Row Created]@row) / IF([Reporting Frequency]@row = "Weekly", 7, IF([Reporting Frequency]@row = "Bi-Weekly", 14, 28)))) + IF(WEEKDAY(TODAY()) >= 5, 1)) * IF([Reporting Frequency]@row = "Weekly", 7, IF([Reporting Frequency]@row = "Bi-Weekly", 14, 28))) + 1
-
Thank you! This worked perfectly!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!