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

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

SecondsStartSecondsEndSecondsHoursMinutesHH:MM:SS
000270 
000270 
000270 
000270 
31662932400259 
30309032400268 

 

the HH:MM:SS is only showing value till 23:59:59 after that its having blank.

 

jdunkerley79
ACE Emeritus
ACE Emeritus

Odd don't see that on mine:

2016-08-20_08-23-01.jpg

 

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

sekarsdream
8 - Asteroid

haa , in that case it worked for you not sure why its not working for me :).. is there any way i can eradicate this ??

sekarsdream
8 - Asteroid

Haa i corrected it anyways i created seperate pasleft and joined later....

sekarsdream
8 - Asteroid

oh my bad i thought its working but its not working :(.. still am not geting values above 24

jdunkerley79
ACE Emeritus
ACE Emeritus

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

sekarsdream
8 - Asteroid

its workign now when i splitted .. thanks a lot for your help on this workflow..

DHB
8 - Asteroid

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

DHB
8 - Asteroid

Hi I think I might have it.  The data types were wrong.  I'll let you know how I get on.

DHB
8 - Asteroid

All good, it's working without a hitch now.  Thanks for the workflow, it's saved me a lot of time.

Labels