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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!