Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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
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')
)
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
You are right, it's for a chart
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
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
I mean these events for each row should occur in one day. And what does it show? I tested yesterday and it worked.
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.
Ah, got it. This is much more interesting but maybe later