Days Difference between 2 dates excluding Weekends (Sat & Sun)

[itsmeanuj]

Hi,

I have a huge dataset where I want to calculate the difference between 2 dates including only Business days (i.e. I just need to exclude weekends). Below is the sample data with the expected output. Data is in the DD-MM-YYYY Format.

First Date	Shipment Date	Difference (Expected Output
10-06-2020	12-06-2020	2
03-08-2020	07-08-2020	4
28-08-2020	28-08-2020	0
07-10-2021	13-10-2021	4
09-07-2020	09-07-2020	0
09-07-2020	16-07-2020	5

[BrandonKO]

Here is a great thread going over how to do this: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Datetimediff-using-only-weekdays-busin...

[Felipe_Ribeir0]

Hi @itsmeanuj 

One way of doing this

[KSowers]

Hello, Here is workflow that should work for this example! Let me know if you need anything else!

[ShankerV]

Hi @itsmeanuj 

Please find the below expected result.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[ShankerV]

Hi @itsmeanuj 

There will a difference in your desired output and mine as because I think you have ignored to eliminate the weekends for the below dates. It should be 0 and 2 respectively.

Please let me know if you need your logic to be revisited. So can do the needful.

Many thanks

Shanker V

[itsmeanuj]

Hi all,

Thanks for your response on this problem. But the suggested solution includes multiplying rows in the process which is not feasible for my data. My data includes millions of records already. We also need to consider that if any of the 2 dates fall on a weekend then we should exclude that as well. please see the example below  

First Date	Shipment Date	Difference (Expected Output
17-10-2021	24-10-2021	5

[ShankerV]

Hi @itsmeanuj 

Addressing the issue 1:

Please find my revised workflow to share the solution for the recent issue raised.

For both the dates I have tested and working fine. 

17-10-2021 Sunday

16-10-2021 Saturday

Many thanks

Shanker V

[ShankerV]

Hi @itsmeanuj 

Addressing the issue 2:

I don't think we have any other way to reach your solution except using Generate Rows.

To overcome the issue, I can suggest you to filter the records into parts less than millions and run the workflow.

or

Using the macro. 

As I do not know whether you are connected to a database and updating the output directly to database or working from excel or any other source. Hence that decision needs to be made in real time.

Many thanks

Shanker V

[RogerS]

I developed a network days macros that does this.  You can also pass in a list of Holidays to remove in addition.  I attached the macro and a link to an  additional macro that pulls federal holidays.  If this helps, please accept as a solution and star the below macro post.  Thx!

https://community.alteryx.com/t5/Community-Gallery/US-Federal-Holidays/ta-p/1055939