Getting Previous Week excluding Weekends and Holidays

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

Workflow

Date Time

Accepted answers

KamenRider

Hi @binu_acs

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

filterlast week.PNG

filterlast week2.PNG

4. Use join tool to remove or separate the holiday

joint tool.PNG

Will close this inquiry. Thank for your continued assistance.

Kamen

All comments

binu_acs

@KamenRider one way of doing this

Previous_day_24062024.yxmd

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

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?

date dataPNG.PNG

Please advise.

Kamen

binu_acs

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

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

binu_acs

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703