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

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

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

jdunkerley79
ACE Emeritus
ACE Emeritus

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)

 

 

sekarsdream
8 - Asteroid

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

sekarsdream
8 - Asteroid

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

jdunkerley79
ACE Emeritus
ACE Emeritus

Attached with no addin need and in 10.0

sekarsdream
8 - Asteroid

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

jdunkerley79
ACE Emeritus
ACE Emeritus

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

 

sekarsdream
8 - Asteroid

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

Labels