Need help in calculating work days excluding the weekends and public holidays

[jerometyl]

Need help in calculating work days excluding the weekends and public holidays. Can someone please teach me how to do it? I need the work days difference between each column of date. 

[binuacs]

@jerometyl Can you provide some expected results? 

[JamesCharnley]

Not sure it's the most efficient but it should be effective. I'm generating a row for every date between the two columns, filtering out weekends, and then outer joining against our list of public holidays to exclude those as well. Then I'm getting a count of how many rows are left as dates (-1 after to account for the start date).

That process has just been repeated for the length of time between QUD and Vendor award to get two separate results columns. (Could in theory be done in one stream if you had more columns and didn't want to repeat but I find it easier to follow like this)

[jerometyl]

Submitted	Quotation Uploaded Date	Vendor Award			Days between Column A and B	Days between Column B and C
27-Jan-21	2-Mar-21	3-Mar-21			23 days	1 day

I hope i counted correctly

[annedione]

Hello,

I've worked on a similar use case in the past with a macro working days generator. I hope this exemple can help you get your answer, I used your holiday file in this instance to generate the number of working days.

 You can work around it to account for additional columns as well.

Best,

Anne

[binuacs]

@jerometyl One way of doing this