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

[rwunderlich]

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

[andrey_krylov]

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

)

[segerchr]

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

‌

[andrey_krylov]

You are right, it's for a chart

[segerchr]

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

[marcus_sommer]

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

[andrey_krylov]

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

[segerchr]

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.

[andrey_krylov]

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