Networkdays does not include holidays although correct syntax - Which settings affect the function?

[derChristopher]

Dear all,

I try to use the networkdays() function in a load script, to evaluate the runtime of tickets (services requests/incidents) according to the working/business hours of my company. Therefore I came up with a solution based on other community posts to calculate the runtime based on a 10hours day excluding weekends.  The final step is now to exclude the holidays as well. Consequently I created a variable with the holidays in regular date format (e.g. 43586 -> 01/05/2019 DD/MM/YYYY). 

So far, so good. Everything should work and works in a example application but not in production application. I think there is some variable or setting which is in conflict with networkdays(). Is that possible? 

Please take a look at my code snippet. As result RuntimeVar returns a runtime of 52:41:38 which is correct. The production application returns 62:41:38 instead, because it neglected the first of may holiday. I have no idea why this happens. The code is the same in both apps. I appreciate any help/suggestions! 

Kind regards,
Christopher

LET vStartHour = 7;
LET vEndHour = 17;
LET vWorkingHourPerDay = $(vEndHour) -$(vStartHour);

SET calc_laufzeit = 'Interval(rangesum(
NetWorkDays($1+1,$2-1,$(vHolidays)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays($2,$2,$(vHolidays)),Rangemin(rangemax(frac($2),maketime(7)),maketime(17))-Rangemax(rangemin(frac($2),maketime(7)),maketime(7)),0) // working hours last day
,if(NetWorkDays($1,$1,$(vHolidays)),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(7)),0) // working first day
,if(NetWorkDays($1,$1,$(vHolidays)) and floor($1)=floor($2),-MakeTime(10)) // correct for first equals last day
))';

 Holidays:
LOAD * INLINE [
Holiday
43586
43615
43626
43825
43741
];

Let counter=0;
FOR Each a in FieldValueList('Holiday') //FEIERTAGE.FEIERTAG
Let vHolidays=if($(counter)=0,chr(39) & '$(a)' & chr(39),'$(vHolidays)' & ',' & chr(39) & '$(a)' & chr(39));
Let counter = 1;
NEXT a;

Data:
LOAD *,
if(1,$(calc_laufzeit(START_TIME,END_TIME)),null()) as RuntimeVar;
LOAD *,
timestamp(timestamp#(StartTime,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,
timestamp(timestamp#(EndTime,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME
Inline [
TicketNo,StartTime, EndTime
1, 4/29/2019 2:12:43 PM, 5/7/2019 4:54:21 PM];

[derChristopher]

I double checked it and found my mistake. The incorrect result was produced due to a wrong timestampformat in a preceeding step. Thank you for troubleshooting! I appreciate it 🙂

[sunny_talwar]

So dev has one format and production has another? Can you change the one in dev to see if that causes the result to change and become incorrect?

[derChristopher]

"Dev" was just the application example I made up for the community. Everything is fine. Thank you very much! 

[sunny_talwar]

Yes that is what I meant... so everything works now? What did you end up doing to resolve it?

[derChristopher]

Yes everything works now. As I said before. The issue was resolved by the correct formatting of the input timestamp in a preceeding step.  

[hadari]

Hi! If I want my endhour to be 20:30 pm,how do I write this variable? It calculate correctly of I write 20 as 20:00 pm but for 20:30 I wrote 20.5 and it doesnt work well.