I am trying to create Workflows based on varying frequency and day row is added.

Options
✭✭✭

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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"?

• ✭✭✭
Options

Hey there! New rows will only be added on Monday.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

Thank you! This worked perfectly!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!