Time Calculations

[kamielrajaram]

Good day all,

Please could you advise on the following. I have a table where I calculate time and the total is not showing corretly. The total number of hours should be about 43 odd hours. Below is the table

Date	TIME(SUM(DISTINCT stafftime)/24/60/60,'hh:mm:ss')	TIME(SUM( stafftime)/24/60/60,'hh:mm:ss')	col3	time(col3/24/60/60,'hh:mm:ss')
	19:52:16	20:01:45	157 936	19:52:16
2011/07/05	08:47:20	14:12:00	31 640	08:47:20
2011/07/06	08:29:04	08:43:12	30 544	08:29:04
2011/07/01	08:58:50	08:58:50	32 330	08:58:50
2011/07/04	08:58:23	23:21:12	32 303	08:58:23
2011/07/03	05:07:40	05:07:40	18 460	05:07:40
2011/07/02	03:30:59	07:38:51	12 659	03:30:59

Thanks in advance

Kamiel

[nagaiank · Accepted solution]

The maximum value of hours in hh:mm:ss returned by time() function is 23. If you convert seconds greater than 24x60x60 into hh:mm:ss format using time() function, result will not show hours correctly.

Using the following expression seems to give results you want for the values in seconds in col3.

Floor((col3/60/60)) & ':'& time(col3/24/60/60,'mm:ss')

[kamielrajaram]

Thank you Krishnamoorthy

[jedgson]

Or you could change the format from Time to Interval.

Jay

[nagaiank]

Using the interval() as suggested by jedson is a better solution.