# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Calculate Office Hours

Hey,

maybe you techs could help me by coding a KPI.

I have two dates. Createddate und Closeddate. Now i want to calculate the runtime.

Closeddate-Createdate ---> But only in office hours. The runtime should only be calculated between Mo-Fr / 06:00-23:00.

I do not have any idea, how to do this.

BR Christian

1 Solution

Accepted Solutions
Contributor III

## Re: Calculate Office Hours

Try Stefan's expression, works fine

Re: Calculate hours between two Date/Time strings

Interval(

Sum(rangesum(

NetWorkDays(Createdate+1,Closeddate-1) * MakeTime(17)

,if(NetWorkDays(Closeddate,Closeddate),Rangemin(rangemax(frac(Closeddate),maketime(6)),maketime(23))-Rangemax(rangemin(frac(Closeddate),maketime(6)),maketime(6)),0)

,if(NetWorkDays(Createdate,Createdate),Rangemin(rangemax(frac(Createdate),maketime(23)),maketime(23))-Rangemax(rangemin(frac(Createdate),maketime(23)),maketime(6)),0)

,if(NetWorkDays(Createdate,Createdate) and floor(Createdate)=floor(Closeddate),-MakeTime(17))

))

,'hh:mm')

19 Replies
MVP

## Re: Calculate Office Hours

NetWorkdays(Createddate , Closeddate) will give you the number of M-F days between the dates.  But you seem to imply there is also a time of day component?

-Rob

Contributor III

## Re: Calculate Office Hours

Hi Christian,  as an idea if  the Createdate and Closeddate dates are  the same

Sum({< Createdate= {"=not Match(WeekDay(Createdate), 'Sun', 'Sat')"} >}

Interval(

RangeMin(Date(Closeddate, 'YYYY-MM-DD hh:mm'), Date#(Date(Closeddate, 'YYYY-MM-DD')&' 23:00', 'YYYY-MM-DD hh:mm'))

-

RangeMax(Date(Createdate, 'YYYY-MM-DD hh:mm'), Date#(Date(Createdate, 'YYYY-MM-DD')&' 06:00', 'YYYY-MM-DD hh:mm'))

,'hh:mm')

)

New Contributor II

## Re: Calculate Office Hours

Thanks for the code. I will try that tomorrow.

This script is for a chart, Not for the load Script to create a variable. Isnt it ?

BR christian

Contributor III

## Re: Calculate Office Hours

You are right, it's for a chart

New Contributor II

## Re: Calculate Office Hours

Hey, i try to get the right data output, but at the moment it doesn`t work. Want do you mean with:

as an idea if  the Createdate and Closeddate dates are  the same ?

BR Christian

## Re: Calculate Office Hours

I just played a bit with it and if your workingdays are only Mo-Fr you could use an approach like this one:

For these few records it fits but I assume there are other constellations which needs further adjustments but I think it shouldn't be too difficult to extend the logic.

Another possibility would be to create an isolated master-calendar with times (maybe the hours would be enough) and to flag there the workingdays (unless weekends and holidays) and the office-hours with 1 and also creating a record-id (with recno/rowno) which is mapped to your createddate and closedate and also served as a lookup-value for a set analysis and then you could use an expression like:

sum({< [id-calendar] = {">=[id-created]<=[id-closed]"}>} FLAG)

Here you will find a similar but a bit more complex scenario - maybe it's helpful in any way: Re: Timestamp / Zeitstempel : Berechnung Start ohne Pausen.

- Marcus

Contributor III

## Re: Calculate Office Hours

I mean these events for each row should occur in one day. And what does it show? I tested yesterday and it worked.

New Contributor II

## Re: Calculate Office Hours

Hey, it could be that createdate is on monday and closedate on wednesday. With this constellation, the script is not working ? I try to create sample data to share. Thanks for helping @all.

Contributor III

## Re: Calculate Office Hours

Ah, got it. This is much more interesting but maybe later