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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
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