Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Leverage your QlikView investment to modernize BI – see how! Join Group
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Find hours between dates excluding weekend and non working hours

Hi All,

I was asked this help that, we have to find the hours between dates where we need to exclude week end and non working hours. I found the solution and I thought of sharing this here so it may useful for some one.


here vSworkhrs = process starting hour and vEworkhrs = process end hours.





2 Replies

Adjustment to requirements

Hi All,

I developed a solution for a ticket system and had also to get the timedifference between ticket open and first working step on the ticket. I developed an own method which is more complicated than the solution of Kumaran! 

But I had to adjust the solution due to the requirements of the ticket system, because a ticket should be transfered to first working step soon (mostly within seconds, but a lot of tickets are transferred within days!). And a ticket could be opened or ended out of working hours. So I needed to take care of this.
Besides weekends, public holidays should also not be regarded. I Used the customers calendar and extracted the public holidays in the variable vFreeDays in format 'YYYY-MM-DD','YYYY-MM-DD',.....

BEGTIME and ENDTIME have format TIMESTAMP! vWorkStart and vWorkEnd have format Time (hh:mm)

My solution :

if (NetWorkDays(BEGTIME,ENDTIME,$(vFreeDays))=1, // if task starts and ends on same day!!
   if ((time(frac(BEGTIME))>$(vWorkEnd)) or (time(frac(ENDTIME))<$(vWorkStart)), // if start or end time are out of working hours, set to 0!
// otherwise just subtract the start time from end time ( open: 15:00,  end: 19:00, working end 18:00,  result in 3 hours!
Rangemin(time(frac(ENDTIME)),$(vWorkEnd)) // if ticket end is after working hour, take end of working hour !!
-Rangemax(time(frac(BEGTIME)),$(vWorkStart))  //if ticket was opened before working hour, take start of working hours
)), // if ticket is handled over at least 2 days
+((NetWorkDays(BEGTIME,ENDTIME,$(vFreeDays))-2)*($(vWorkEnd)-$(vWorkStart)))))) as Duration;

So thank you Kumaran for your idea!

Contributor II
Contributor II

Re: Adjustment to requirements

Thanks and it is great!!