Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Working Hours calculation excluding weekends and Holidays

sekarsdream
8 - Asteroid

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?

 

30 REPLIES 30
jdunkerley79
ACE Emeritus
ACE Emeritus
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
8 - Asteroid

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 TimestampDate-testWeekdaysHolidaysHoursStartHoursEndHours
7/4/2016 18:337/4/2016 12:3210-2032120

so considering this case the hours is wrong ..

First Quote TimestampDate-testWeekdaysHolidaysHoursStartHoursEndHours
5/30/2016 12:375/30/2016 12:241013204-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
ACE Emeritus
ACE Emeritus

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
ACE Emeritus
ACE Emeritus

Try the attached.

 

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

sekarsdream
8 - Asteroid

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
8 - Asteroid

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
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

Try the attached.

 

Added a unique tool to select unique days

Altered to compute in seconds

Added a formatted date output

sekarsdream
8 - Asteroid

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

First Quote TimestampDate-testStartHoursEndHoursBD TATMinutesSecondsHHMMSS
        
06/10/2016 13:33:1606/10/2016 13:17:464.2833334.450:1515590:15:59
06/06/2016 14:44:5806/06/2016 14:30:225.53.2666670:141400: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
ACE Emeritus
ACE Emeritus

Adjust calculation to be in seconds rather than minutes 

 

 

Labels