Calculate Office Hours

[segerchr]

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

[swuehl]

Maybe have a look at

Calculate hours between two Date/Time strings

It shows some different approaches ( script / chart expression) to calculate the interval excluding e.g. non working hours.

[segerchr]

Hey, in that way i got the hours between. But to get the hours without workdays and only from 06:00 to 23:00 ??

[andrey_krylov]

Nothing new under the sun))

[andrey_krylov · Accepted solution]

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')

[segerchr]

Hey,

i got not relaistic data. Do you know why ? Because of AM and PM isntìt ? So i have to load the data in /24 format ?

[segerchr]

Thanks for your answer.

[swuehl]

Can't reproduce this here

Please post some sample records as INLINE table.

[andrey_krylov]

That's what I see in my app with this expression.

Not sure, but check these variables and try these values

SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss';

[segerchr]

Thanks for all the help. Skript is now working.

[segerchr]

Hey, maybe you guys could help me with the last calculation. 

I used the script, which i working fine, to get the hours. Now i have pausedminutes which can be 1710 Minuten. I convert the minutes into the same unit. When i try to subtract the pausedminutes from the hours, i get strange numbers. 

To get hours from minutes i use the following code: 

Interval(PausedMinutes/24/60,'hh.mm')

The script for the hours:

Interval(

Sum(rangesum(

NetWorkDays(CreationDate+1,ClosedDateTick-1) * MakeTime(17)

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

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

,if(NetWorkDays(CreationDate,CreationDate) and floor(CreationDate)=floor(ClosedDateTick),-MakeTime(17))

))

,'hh.mm')

Now i try to subtract:

(Interval(

Sum(rangesum(

NetWorkDays(CreationDate+1,ClosedDateTick-1) * MakeTime(17)

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

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

,if(NetWorkDays(CreationDate,CreationDate) and floor(CreationDate)=floor(ClosedDateTick),-MakeTime(17))

))

,'hh.mm'))-(Interval(PausedMinutes/24/60,'hh.mm'))

The result should be: 00.47

BR Christian