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?
Solved! Go to Solution.
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
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
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)
ALso am not familiar to add a add in to the alteryx can you help me on that as well
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
Woow guess this will work, just one more last question why you have used the Union tool in this workflow ???
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
Thanks its working suppose if i need the final TAT in hours:MM :Sec what can i do?