How To: Calculate the difference between two dates excluding weekends including time

[wale_ilori]

I've put together an alteryx workflow to calculate the difference between two dats in datetime format that gives an output calculated to the minute, not just to the day. This question has come up several times and while there are  few responses on it here, thought i'd rethink it from scratch.

This workflow details the steps you need to remove weekends between two dates and is therefore, more accurate than the network days you get from excel. The output is in days but multiplying by 1440 will give you the duration in minutes.

The workflow first trims the datetime to dates, determines the duration in whole days and then takes the hours and minutes and applies math to either add or subtract the fractional hours in minutes (converted to days) and adds that to the duration calculated previously.

The workflow has some sample data to view the output. Let me know if you have any issues or questions. If this works well, I have some ideas of incorporating holidays as well..

[Jake_NG]

This works great and I would love a way to add holidays!

[Jake_NG]

I think I figured out a quick way to add holidays - between "Generate rows for each day" and "Formats each date as a day of the week", include a filter with your holidays. In this case, I did UK bank holidays 2020/2021 - 

ToDate([Date]) != "2020-01-01" AND
ToDate([Date]) != "2020-04-10" AND
ToDate([Date]) != "2020-04-13" AND
ToDate([Date]) != "2020-05-08" AND
ToDate([Date]) != "2020-05-25" AND
ToDate([Date]) != "2020-08-31" AND
ToDate([Date]) != "2020-12-25" AND
ToDate([Date]) != "2020-12-28" AND
ToDate([Date]) != "2021-01-01" AND
ToDate([Date]) != "2021-04-02" AND
ToDate([Date]) != "2021-04-05" AND
ToDate([Date]) != "2021-05-03" AND
ToDate([Date]) != "2021-05-31" AND
ToDate([Date]) != "2021-08-30" AND
ToDate([Date]) != "2021-12-27" AND
ToDate([Date]) != "2021-12-28"

Additionally, I found if the start and end date were on the same day it would return a null. So I added a formula between the "Combine records together" formula and "Deselect unneeded columns" select to include the following

IF IsNull([# of Week Days])  THEN [Duration (Days) Incl Wkends] else [Duration (Days) Excl Wkends] ENDIF