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