Getting Previous Week excluding Weekends and Holidays

[KamenRider]

Hi,

May I ask for the formula that will get previous week, not including the weekend and holidays if possible. In this case, if the day is either from 6/24 to 6/28, the output will show me June 17, 18, 20 and 21. June 19 is skip because it is a holiday.

 

Please advise.

Kamen

[binuacs]

@KamenRider one way of doing this

[c-lopez]

Hi @KamenRider,

This has been solved already in other posts :) here is a great one that you can use as a base, then simply transpose dates to make them column title.
One major thing to note: You need to define what is a holiday in your world the list provided in the community post I linked is a great base but if your organization follows a non-standard holiday then you want to enter manually into the list of holidays.

I hope this helps!

Regards 

[KamenRider]

Hi @binuacs 

Thanks for the reply. What if my data look like this as shown below. How can I get the previous dates excluding the weekends and holidays?

Please advise.

Kamen 

[binuacs]

@KamenRider you can exclude the weekends using the formula in a filter tool DateTimeFormat([Date],'%A') NOT IN ('Sunday','Saturday'). For the Holiday , you need to specifically provide the holidays as an input then using a join tool you can exclude the holidays as well.

[KamenRider]

hI @binuacs 

The formula only filtered out dates that falls on Saturday and Sunday. How can I filter in the previous week? My requirements need only to show 06/17/2024 to 06/21/2024 in instance since our date today is 6/25. Then I think next is to exclude the holidays which is 06/19.

Please advise.

Kamen

[binuacs]

@KamenRider are you expecting some thing like below. I mean exclude the highlighted rows, and today is 25/6 then previous week same say is 18/6

[KamenRider]

Hi @binuacs 

For the Saturday and Sunday (weekends), yes I would like it remove or not included in the list. If today is 6/25 or 6/26, I would like the list of previous week (business days) from Monday to Friday which is June 17 to 21. Another sample like for next week, if I run it 7/1, expected results would be 6/24 to 6/28. I just need the previous 5 business days.

Then like as you said, we can extract the holidays using the join tool via the text input tool that list the US holidays.

Please let me know if you have questions.

Thanks,

Kamen

[KamenRider · Accepted solution]

Hi @binuacs 

I think got a solution finally on this.

1. First, we filtered the Saturday and Sunday

 

2. Compute the Weekstart, LastweekStart and LastWeekEnd

3. Filtered the last week date

 

 

4. Use join tool to remove or separate the holiday

 

Will close this inquiry. Thank for your continued assistance.

Kamen