Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have calculated the number of seconds from one time to another.
I have a field called NewPickTime and when I sum the TOTAL seconds between 2 times I get an answer of 1787491
I want to convert this into hours and minutes, but as a decimal, so it would be 496.5253
However, when I try I keep getting weird numbers.
I have this in my script to calculate the number of seconds. This is correct and I have verified it.
INTERVAL(EndTime-TIME(RANGEMAX(EndPreviousTime, EndTime-RANGEMIN(PickTime, '00:05:00'))),'s') AS NewPickTime
When I create a text object and say =SUM(NewPickTime)/60/60 I get a result of
instead of 496.5253.
Does anyone have any ideas as to what I am doing wrong here?
Regards
Alan
Your answer 1787491 is actually a value of 20.668 days formatted to be shown as seconds. Then interval function returns a number of days and formats it as seconds because of the 's'. So you're off by a factor of 24. Try multiplying NewPickTime by 24.
Hi Alan,
Could you upload a sample file with this issue?
Regards,
Luis.
Your answer 1787491 is actually a value of 20.668 days formatted to be shown as seconds. Then interval function returns a number of days and formats it as seconds because of the 's'. So you're off by a factor of 24. Try multiplying NewPickTime by 24.
Alan,
NewPickTime contains duration in seconds
Sum(NewPickTime) should also contain seconds but it is not the case: it will return a hh:mm;ss format. If you divide it by 3600, you will get very strange result (I did get strange results)
You need to use interval a second time: sum(interval(newpicktime, 's'))/60/60 to get hrs (but I think you need an extra /24 to get the part of the day)
Fabrice
Maybe you could also try:
num#(INTERVAL(EndTime-TIME(RANGEMAX(EndPreviousTime, EndTime-RANGEMIN(PickTime, '00:05:00'))),'s'))
Hope this helps!
Hi Gysbert,
Yes that was the problem, I had tried this earlier and I got the same result as what you suggested.
However I did not understand why I was getting it, now it makes sense
Thanks
Regards
Alan