Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey All!
I have an output that provides the time based on milliseconds, I originally used this to calculate the days/hours for each incident:
interval
(TIMETAKEN / (24 * 60 * 60 * 1000),'DD:hh:mm')
This worked great, but rather than basing it on a 24 hour day, we want to use a 7.5 hour day (working day). When I change 24 to 7.5 my figures get skewed so instead of showing 0.1.30 it gives me 0.4.48.. Any idea what I should do?
Many thanks for your help!
Kind Regards,
Dayna
Dayna
If I am not mistaken, TIMETAKEN is in milliseconds. For a day length other than 24 hours, you have to calculate the days and hours separately.
The total number of hours is:
TIMETAKEN / (60 * 60 * 1000)
The days (which are the integer part of the interval) will be:
floor(TIMETAKEN / (60 * 60 * 1000) / 7.5)
The hours will be the remainder:
frac(TIMETAKEN / (60 * 60 * 1000) / 7.5) * 7.5
(can't use the Mod function as this only for integers)
But the hours must be divided by 24 to get the fractional part of the interval. So the resulting expression is:
interval(floor(TIMETAKEN / (60 * 60 * 1000) / 7.5) +
frac(TIMETAKEN / (60 * 60 * 1000) / 7.5) * 7.5 / 24)
See the attached as an example.
Hope that helps
Jonathan
Hello All!
Does anyone have any ideas?
Kind Regards,
Dayna
Hi Dayna,
Have you tried this:
(TIMETAKEN / (270000),'DD:hh:mm')
?
Dayna
If I am not mistaken, TIMETAKEN is in milliseconds. For a day length other than 24 hours, you have to calculate the days and hours separately.
The total number of hours is:
TIMETAKEN / (60 * 60 * 1000)
The days (which are the integer part of the interval) will be:
floor(TIMETAKEN / (60 * 60 * 1000) / 7.5)
The hours will be the remainder:
frac(TIMETAKEN / (60 * 60 * 1000) / 7.5) * 7.5
(can't use the Mod function as this only for integers)
But the hours must be divided by 24 to get the fractional part of the interval. So the resulting expression is:
interval(floor(TIMETAKEN / (60 * 60 * 1000) / 7.5) +
frac(TIMETAKEN / (60 * 60 * 1000) / 7.5) * 7.5 / 24)
See the attached as an example.
Hope that helps
Jonathan
Hello All,
Jonathan, that's brilliant!! I have one query tho:
It works perfectly when 7.30 is being added (as one entry) but not when there's two entries of 5 (i.e. one on each day or two different records..)
Any idea?
Kind Regards,
Dayna
Aha, my sum's weren't working correctly in the load script - in the expression it works perfectly.
Thanks!
interval(floor(sum(SUN_IH_Time) / (60 * 60 * 1000) / 7.5) +
frac(sum(SUN_IH_Time) / (60 * 60 * 1000) / 7.5) * 7.5 / 24,'D-hh:mm')