Working Hours calculation excluding weekends and Holidays

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?

Workflow

Accepted answers

jdunkerley79

Attached with no addin need and in 10.0

Work Hours No JD.yxmd

All comments

jdunkerley79

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

sekarsdream

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

sekarsdream

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

jdunkerley79

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703