community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Working Hours calculation excluding weekends and Holidays

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.

 

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

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 ??

Asteroid

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

Asteroid

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

Asteroid

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

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

Asteroid

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

Asteroid

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

Labels