Creating Report Workflow Automation that goes out on the 3rd Friday following a Monday

CurryKE

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

Paul Newcome

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?

CurryKE

Yes Paul that is correct. I was thinking I would have to use a formula instead of the automation workflow functions for this.

Paul Newcome

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.

Paul Newcome

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:

CurryKE

I will give this a try tomorrow and report back on if it works. Thanks so much for your input here!

Paul Newcome

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.

L_123

=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.)

Paul Newcome

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.

L_123

@Paul Newcome NP. You had the correct concept, I just finished it. There are 2 keys to my solution

1. instead of using the first day of the month, use the last day of the previous month
2. 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.

Paul Newcome

@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.