Working Hours calculation excluding weekends and Holidays

[sekarsdream]

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?

[jdunkerley79]

Little fiddly but I would do the following.

1. Count the weekdays between start and end including both (my add in can do this: https://github.com/jdunkerley/AlteryxFormulaAddOns/wiki/Date-and-Time-Functions-%28DateUtils.xml%29)

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

[sekarsdream]

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???

Thanks 

~Raj

[jdunkerley79]

Sorry took me a few minutes to build.

To compute lost hours on start:

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

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)

[sekarsdream]

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

[sekarsdream]

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

[jdunkerley79 · Accepted solution]

Attached with no addin need and in 10.0

[sekarsdream]

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

[jdunkerley79]

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

[sekarsdream]

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