Creating Report Workflow Automation that goes out on the 3rd Friday following a Monday
Basically, I can only use weeks where there is a Monday in that month before the 3rd Friday. So the native function of picking 3rd Friday will not work for this scenario. I have seen lots of help for Monday's but am not sure how to program this one.
I need to send out a report reminder to owners the 3rd Friday following a Monday every month. And then trigger to send that report to the manager the following Friday.
Thanks for your help here!
Kristi
Answers
-
So you want to find the third Friday that falls AFTER the first Monday even if that happens to make it the fourth Friday of the month?
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!
-
Yes Paul that is correct. I was thinking I would have to use a formula instead of the automation workflow functions for this.
-
Yes. You are going to have to use a formula, and that formula is going to be a little tricky figuring out. @L_123 is pretty genius with these types of things. I will work on trying to figure it out as well because I do feel like this should be possible. I remember that somewhere here in the community is a solution for finding the first Monday in the month. From there we would just need to add 25 days.
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!
-
Ok. So I wasn't able to find that thread, but if you don't mind creating a reference table we can make it work.
Reference table will look like the below. It can either be on the same sheet or a different sheet. The solution formula is referencing the table being on the same sheet, but you can replace the ranges with cross sheet references if you decide to use a different sheet. This solution also has the month and year as numbers in their own columns. We can tweak this depending on your exact setup as needed, but here is the basic idea...
Reference Table:
Solution Formula:
=DATE(Year@row, Month@row, 1 + INDEX(Add:Add, MATCH(WEEKDAY(DATE(Year@row, Month@row, 1)), Day:Day, 0))) + 25
Data Set:
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!
-
I will give this a try tomorrow and report back on if it works. Thanks so much for your input here!
-
Sounds good. Of course we can tweak it to reference a date column instead of having the month and year separated out. I am also sure there is a way to do it without a table that involves more math based functions such as MOD and ROUND and all of that other fun stuff, but I haven't quite figured that particular bit out yet.
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!
-
=DATE(YEAR(A@row), MONTH(A@row), 18 + IF(WEEKDAY(DATE(YEAR(A@row), MONTH(A@row), 1) - 1) = 1, 2, 9) - WEEKDAY(DATE(YEAR(A@row), MONTH(A@row), 1) - 1))
give that one a try. Sorry I just saw this a couple minutes ago, this is a fun problem. (Column reference I used is "A", and it just needs a day somewhere in the month you want to look at.)
-
I knew there had to be a way to do it without a table and that if anyone could figure it out it would be you. Haha. Thanks @L_123.
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!
-
@Paul Newcome NP. You had the correct concept, I just finished it. There are 2 keys to my solution
- instead of using the first day of the month, use the last day of the previous month
- realizing that on your table 5/7 of the values add up to 9 (when you use the last day of the previous month it ends up being 6/7)
I think it can be simplified a bit by subtracting 10 and removing the conditional, but I only had time to try that once, and wasn't able to get it to work.
-
@L_123 That's a great point. I hadn't noticed that most of them add up to 9. It makes sense how you came to your solution now.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!