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.
This is working perfect but another problem with this is since we use padleft and 2 digit.. after 24 hours whatever the data its coming its showing the HH:MM:SS as blanks for example
Seconds | StartSeconds | EndSeconds | Hours | Minutes | HH:MM:SS |
0 | 0 | 0 | 27 | 0 | |
0 | 0 | 0 | 27 | 0 | |
0 | 0 | 0 | 27 | 0 | |
0 | 0 | 0 | 27 | 0 | |
31 | 6629 | 32400 | 25 | 9 | |
30 | 3090 | 32400 | 26 | 8 |
the HH:MM:SS is only showing value till 23:59:59 after that its having blank.
Odd don't see that on mine:
The pad left should only guarantee a minimum of 2 numbers. It doesn't care if more. Has to be stored as a string type (this is the case in my example) not a time though as I don't think Alteryx likes times beyond 23:59:59
haa , in that case it worked for you not sure why its not working for me :).. is there any way i can eradicate this ??
Haa i corrected it anyways i created seperate pasleft and joined later....
oh my bad i thought its working but its not working :(.. still am not geting values above 24
Can you screen shot the formula tool and I'll see if I can spot anything. Only guess I have is the datatype being time for the column
its workign now when i splitted .. thanks a lot for your help on this workflow..
Hi, I'm trying to adapt your workflow to my own data and have hit a snag with the last formula tool. The business hours in my model are all day (24 hours) Monday to Friday excluding holidays.
For StartHours I've modified the formula so it reads;
IF Left([ENTRY_POSTED_AT],10) = [FirstHoliday] or Left(DateTimeFormat([ENTRY_POSTED_AT],"%a"), 1) = 'S' THEN
0
ELSE
DateTimeDiff([ENTRY_POSTED_AT], Left([ENTRY_POSTED_AT],11) , "minutes") / 60
ENDIF
I'm getting a Null value out of this for ENTRY_POSTED_AT (DateTime) values that are not a weekend or holiday though.
Can you see what I might have done wrong here?
Thank you in advance and also for your workflow which has already saved me countless hours.
DHB
Hi I think I might have it. The data types were wrong. I'll let you know how I get on.
All good, it's working without a hitch now. Thanks for the workflow, it's saved me a lot of time.