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?
Solved! Go to Solution.
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
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
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
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???