Counting number of business days between 2 date ranges, taking holidays into account

[Jes]

I have a table of +100K rows of transactional data and would like to compute the # of business days between 2 date columns.

I would like to calculate the # of business days between [PickupDate] and [SubmitDate].

I can foresee the formula being something along the lines of: [Pickupdate] - [Submitdate] - # of weekends - # of holidays.

I have a separate table that lists holidays for me and wanted to know how I can compute the actual # of business days between the 2 date columns?

I took a look at this solution: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-a-Date-to-Day-of-the-Week/td-p...

but the example provided is dealing with single row of data.

Given the # of datapoints that I am working with, I think I'd need to create a batch macros to use the above method and am not sure how to do this.

Let me know your thoughts.

[DavidSkaife]

Hi @Jes 

I've attached a workflow i pulled together which should meet your requirements if i understand them correctly using some dummy data similar to your screenshot, and dummy holidays.

The inclusion of the Record ID allows you to group the dates created together, this could be replaced by a unique identifier in your data if there is one.

[Qiu]

@Jes @DavidSkaife 
I made a minor change to the flow of @DavidSkaife .
Since you mentioned that there are +100K rows, we better reduce the size of data steam.
Generate Rows is powerfull but can significally increase the size of data stream, which leads to poor performance.

[Jes]

@DavidSkaife  & @Qiu  

Such a simple but brilliant solution.

I'm sure many Alteryx users will find this useful since working with dates is a painful but used across many industries.

I noticed something off about the workflow as is.

The workflow is dropping some transaction data along the way (input: 130,739 rows, output: 130,718 rows).

It seems to be an issue with the summarize tool dropping off some date pairs.

Using SubmitDate = 11/11/21 and PickupDate = 11/11/21 as an example, the summarize tool is not capturing this date pair.

[DavidSkaife]

Hi @Jes 

It seems to be working on mine and @Qiu workflows when i enter in 11/11/21 for both date fields, and i can't seem to replicate the issue. Are you able to share a subset of your data which is causing the issue?

Edit: just had a thought; 11/11 isn't a holiday date in your workflow is it?

[Qiu]

@DavidSkaife  @Jes 
I did a quick test and did not replicate the issue.
I guess we need your test data afterall 😁

[soccertil1108]

For this type of calculation I always recommend the construction of a Date table. Whether you store the data as an Alteryx data table or within an RDBMS makes no difference. The net result is that your Alteryx analytics become simplier. I have a few videos that discuss Date analytics and a Date table. Check them out with this link: https://www.youtube.com/channel/UCY4BmMHlvAlXuypSfAr0Inw/search?query=date

I will be happy to share my Date table with you.

[boglarkalipka]

sorry, wrong post :)