topic Re: Working Hours calculation excluding weekends and Holidays in Alteryx Designer Desktop Discussions

Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 24 May 2018 01:55:04 GMT

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?

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 12:29:47 GMT

Little fiddly but I would do the following.

1. Count the weekdays between start and end including both (my add in can do this: https://github.com/jdunkerley/AlteryxFormulaAddOns/wiki/Date-and-Time-Functions-%28DateUtils.xml%29)

2. Count the number of weekday holidays between start and end

We now have the number of business days (1) - (2).

Multiply this by number of hours per day x 8

Finally, we need to remove the lost hours at start and end.

Will stick an example together

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 18 Aug 2016 12:38:40 GMT

Thanks a lot for your reply but i dint get last line ....

we need to remove the lost hours at start and end.

can you explain this to me ??..

Also how can i count the weekday holiday as i dont see any formula here???

Thanks

~Raj

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 12:46:29 GMT

Sorry took me a few minutes to build.

To compute lost hours on start:

IF Start < Left(Start,11) + '09:00:00' THEN 
	0
ELSEIF Start > Left(Start,11) + '18:00:00' THEN 
	9
ELSE
	DateTimeDiff(Start, Left(Start,11) + '09:00:00', "minutes") / 60
ENDIF

This with use minutes for the middle of day. For End same expression by 9 - the answer.

For weekday holidays, append the holiday calendar to each row and the filter. Finally summarise to get total.

Full sample attached (but uses my add in at moment)

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 18 Aug 2016 12:50:12 GMT

ALso am not familiar to add a add in to the alteryx can you help me on that as well

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 18 Aug 2016 12:55:28 GMT

oops am not able to open the file , i have version 10.1 .. when i checked for updates it said upto date. i cant open the file as its says am not having the lateste version

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 12:57:02 GMT

Attached with no addin need and in 10.0

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 18 Aug 2016 13:06:30 GMT

Woow guess this will work, just one more last question why you have used the Union tool in this workflow ???

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 13:17:07 GMT

No holidays between start and end - I don't want to lose those rows.

~~Think a bug on start / end hours if the start or end are a holiday. In that case, shouldn't remove the hours from~~ total.

Attached bug fixed version with support for holiday on start/end and correction for NULL in ones with holiday between

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 18 Aug 2016 16:14:56 GMT

Thanks its working suppose if i need the final TAT in hours:MM :Sec what can i do?

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 16:32:08 GMT

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!

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Thu, 18 Aug 2016 17:08:55 GMT

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

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 18:58:28 GMT

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

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Thu, 18 Aug 2016 21:25:33 GMT

Try the attached.

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

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Fri, 19 Aug 2016 04:02:12 GMT

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

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Fri, 19 Aug 2016 04:36:33 GMT

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.

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Fri, 19 Aug 2016 04:36:34 GMT

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Fri, 19 Aug 2016 07:28:17 GMT

Try the attached.

Added a unique tool to select unique days

Altered to compute in seconds

Added a formatted date output

Re: Working Hours calculation excluding weekends and Holidays

sekarsdream — Fri, 19 Aug 2016 08:44:31 GMT

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

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

Re: Working Hours calculation excluding weekends and Holidays

jdunkerley79 — Fri, 19 Aug 2016 14:47:24 GMT

Adjust calculation to be in seconds rather than minutes