Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Working Hours calculation excluding weekends and Holidays


Hello ,


I have a data set in which i want to find the TAT between two Date time variable the creteria is we should not include weekends also we should exclude #holiday list which i have, also the working hours is in between 9:00 to 18:00 .I have tried so many ways but not able to figure this out. for example as below one.. 


Timestamp1        Timestamp 2

06/16/2016 7:04:03 AM  06/20/2016 7:52:53 AM


We have  18th and 19th is weekends and 17th is holiday ,can anyone help me on this?


Little fiddly but I would do the following.


1. Count the weekdays between start and end including both (my add in can do this:

2. Count the number of weekday holidays between start and end


We now have the number of business days (1) - (2).

Multiply this by number of hours per day x 8


Finally, we need to remove the lost hours at start and end.


Will stick an example together


Thanks a lot for your reply but i dint get last line ....


we need to remove the lost hours at start and end.


can you explain this to me ??..


Also how can i count the weekday holiday as i dont see any formula here???




Sorry took me a few minutes to build.


To compute lost hours on start:

IF Start < Left(Start,11) + '09:00:00' THEN 
ELSEIF Start > Left(Start,11) + '18:00:00' THEN 
	DateTimeDiff(Start, Left(Start,11) + '09:00:00', "minutes") / 60

This with use minutes for the middle of day. For End same expression by 9 - the answer.


For weekday holidays, append the holiday calendar to each row and the filter. Finally summarise to get total.


Full sample attached (but uses my add in at moment)




ALso am not familiar to add a add in to the alteryx can you help me on that as well


oops am not able to open the file , i have version 10.1 .. when i checked for updates it said upto date. i cant open the file as its says am not having the lateste version


Attached with no addin need and in 10.0


Woow guess this will work, just one more last question why you have used the Union tool in this workflow ???

No holidays between start and end - I don't want to lose those rows.


Think a bug on start / end hours if the start or end are a holiday. In that case, shouldn't remove the hours from total


Attached bug fixed version with support for holiday on start/end and correction for NULL in ones with holiday between



Thanks its working suppose if i need the final TAT in hours:MM :Sec  what can i do?