18 Replies Latest reply: Nov 9, 2018 6:47 PM by Christian Seger

# 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

• ###### 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

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

)

• ###### 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

• ###### Re: Calculate Office Hours

You are right, it's for a chart

• ###### 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 mean these events for each row should occur in one day. And what does it show? I tested yesterday and it worked.

• ###### 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.

• ###### Re: Calculate Office Hours

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

• ###### Re: Calculate Office Hours

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

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

• ###### Re: Calculate Office Hours

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 ?

• ###### Re: Calculate Office Hours

Can't reproduce this here

Please post some sample records as INLINE table.

• ###### 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

• ###### Re: Calculate Office Hours

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.

• ###### Re: Calculate Office Hours

Nothing new under the sun))

• ###### Re: Calculate Office Hours

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

• ###### Re: Calculate Office Hours

Thanks for all the help. Skript is now working.