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]

The result will include minutes as a decimal. So can do something like tostring(hours,0) + ':' + padleft(tostring(mod(hours*60,60),0), 2, '0') in the formula tool

On mobile so no access to alteryx to write this. Think above roughly right!

[sekarsdream]

No actually the output is coming in as minutes not in hours , also for the end coloumn after 18:00 hours its showing as 0 for example for 

7/4/2016 18:33

 as per the formula the end hour is 0 which is wrong  let me share you an example

First Quote Timestamp	Date-test	Weekdays	Holidays	Hours	StartHours	EndHours
7/4/2016 18:33	7/4/2016 12:32	1	0	-203	212	0

so considering this case the hours is wrong ..

First Quote Timestamp	Date-test	Weekdays	Holidays	Hours	StartHours	EndHours
5/30/2016 12:37	5/30/2016 12:24	1	0	13	204	-208

the other example as i said is coming in minutes  as 13 above is in minutes not hours..

Thanks a lot for helping me out on this

[jdunkerley79]

ah the issue is I assumed the timestamps were in Alteryx DateTime format, i.e. yyyy-mm-dd HH:mm:ss

will do an adjusted version parsing input dates 

[jdunkerley79]

Try the attached.

Horribly twisted REGEX_Replace to parse the dates due to no leading 0s.

[sekarsdream]

Thanks Again , but i worked out the logic without the RegEx , but my only issue now is i want the end hours in HH:MM :SS, now as per you suggestion i have got HH:MM by using the formula.tostring(hours,0) + ':' + padleft(tostring(mod(hours*60,60),0), 2, '0')  is there anyways i can bring the Seconds as well??

Regards

Raj

[sekarsdream]

and one  final problem, i know am repeating the same  word is the date 06/06/2016 is causing some issue ..i have attached the data for ur reference. Its showing -ve value and also its giving wrong calculation.

[sekarsdream]

and one  final problem, i know am repeating the same  word is the date 06/06/2016 is causing some issue ..i have attached the data for ur reference. Its showing -ve value and also its giving wrong calculation.

[jdunkerley79]

Try the attached.

Added a unique tool to select unique days

Altered to compute in seconds

Added a formatted date output

[sekarsdream]

Thanks againg , but still the time in seconds is not mathcing the exact difference.for example

First Quote Timestamp

Date-test

StartHours

EndHours

BD TAT

Minutes

Seconds

HHMMSS

06/10/2016 13:33:16	06/10/2016 13:17:46	4.283333	4.45	0:15	15	59	0:15:59

06/06/2016 14:44:58

06/06/2016 14:30:22

5.5

3.266667

0:14

14

0

0:14:00

is there any way to solve this.. also the Unique tool which you icldued dint had any field i just added the holiday in that filed hope thats fine???

[jdunkerley79]

Adjust calculation to be in seconds rather than minutes