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!

Find more posts tagged with

Formulas

Automations

Workflows in Smartsheet

Accepted answers

Paul Newcome

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

All comments

Paul Newcome

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

Kim Johnson

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

Paul Newcome

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:

Kim Johnson

Thank you! This worked perfectly!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.