calculation error with Networkdays() function

[#1]

Hello,

I have a problem with the networkdays () function.

This function takes into account holidays that are not within the calculation interval:

for example:

My date format is as follows:

SET DateFormat = 'DD-MM-YY';

The call of the function:

Networkdays ('16 -01-17 ', '19 -01-17') referrals 4, between 16 and 19 January there are 4 working days: the result is correct.

If now I set the {holiday} parameter by simulating a holiday on 20/01/2017 (so after the 19th)

Networkdays ('16 -01-17 ', '19 -01-17', '20 -01-17 ',' 20-01-17 ') references 3.

I have this problem every time my end date and the day before a holiday.

Have you ever encountered this problem and what solution have you applied?

Thank you for your help

[sunny_talwar]

I was able to replicate the issue only if I added the same date twice in my Holiday... Why are you adding it twice? Is there a reason for adding it twice

[#3]

I apply ( but i certainly not understant) the help of the function:

start_date	The start date to evaluate.
end_date	The end date to evaluate.
holiday	Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas.

in my mind, it was mandatory to specify a start and an end date even if it is the same.

[sunny_talwar · Accepted solution]

Look here

https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkd...

The above description sort of looks confusing, but I don't think you need start and end date... this seems to be working also

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 20), MakeDate(2017, 01, 20))

Giving me 4, just like this is giving me 4

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 19), MakeDate(2017, 01, 20))

[#5]

thank for your help

[sunny_talwar]

No problem at all

[swuehl]

I can only hope that the description in the online help is wrong...

AFAIK, Networkdays() required to list optional holidays, a value per holiday date.

If this has changed, I guess it will break a lot of code.

But I guess it's just one of the wrong descriptions in the Help.

This is the old help text:

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

The new help tells instead:

holiday

Holiday periods to exclude from working days. A holiday period is stated as a start date and an end date, separated by commas. Example: '25/12/2013', '26/12/2013' You can specify more than one holiday period, separated by commas. Example: '25/12/2013', '26/12/2013', '31/12/2013', '01/01/2014'

But looking at the specific values it's hard to tell if it is using interval start end dates or single holiday values.

But we can easily test it:

=NetWorkDays(MakeDate(2017, 01, 16), MakeDate(2017, 01, 19), MakeDate(2017, 01, 17), MakeDate(2017, 01, 19))

returns 2, not 1. So no period is used in holidays (edit: in QV11.20 SR12)

[#8]

That means, if we have several period of two weeks of days off  , we need to specify in the holidays list , for each period, each days of the two weeks.. .

[swuehl]

Right, that's how I understand the function.

[ajayvermaida]

Hi Rob,

can you please help me to calculating the time diff between two dates based on given points

1- First I have to calculate the time weekdays in between 7 Am to 11 PM excluding sat and sun . am using below script for this but prob is that when both the dates are weekend then its also calculate the time diff  .

if(not WildMatch(IssueStatus,'Pending') , (Interval((RangeMin(frac(IssueResolutionDate), MakeTime(23))- RangeMax(frac(IssueCreateDate), MakeTime(7)))+
( NetWorkDays(IssueCreateDate, IssueResolutionDate-1) * MakeTime(16)) ))) as TotalTime,

2- Second I have to calculate the time diff only Weekend Sat or Sun in between 10 Am to 4 PM . also if any dates is weekend and other one is weekday then it calculate the time based on diff time difference .

3- if any ticket open in the last month and closed in next month then it calculate the time as point 1 except if it comes on weekdays .

Please help me to find the correct result 

Thanks

Ajay