community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Working Hours calculation excluding weekends and Holidays

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

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 

Try the attached.

 

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

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

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.

 

 

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.

 

 

Try the attached.

 

Added a unique tool to select unique days

Altered to compute in seconds

Added a formatted date output

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

Adjust calculation to be in seconds rather than minutes 

 

 

Labels