Sorry for the delay in responding. I was out of town in a remote area of Montana with no internet access. Please see attached and below screenshot. I used Jonathan Drummey's calculation to get where you need to be. Also, I noticed that in doing a straight DATEDIFF on your Start and End Date/Time fields, that there are a higher number of seconds captured than what you have for Total Active Agent Time. I'm assuming that there's some corrective action being taken to bring that number down (See Sheet 4 for the straight DATEDIFF results vs. Total Active Agent Time results) as it's quite different.
Here's the calculation by Jonathan Drummey:
//replace [Seconds] with whatever field has the number of seconds in it
//and use a custom number format of 00:00:00 (drop the first 0 to get rid of leading 0's for hours)
IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds
+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes
+ INT([Seconds]/3600) * 10000 //hours
It requires a couple of calculations, each based on previous calculation.
(1) Use the original number of seconds
(2) Create SUM([Original Number of Seconds Field)
(3) Use Jonathan Drummey's calculation above and replace [Seconds] with whatever is named in calculation (2).
(3.1) Format as 0:00:00 not as h:mm:ss.
Hope it helps! Thx, Don
Syncro Report.twbx 81.6 KB
Thanks a lot Don.
The solution that you provided helped me.
Thanks, it is working good for me.
Great post and this approach seem to work for me, however, when I use this calculated field (from Sum(sec) to h:mm:ss) in a running total table calculation, the total number of minutes exceeds 60 (while it should add an hour after that point. It might have something to do with the order of operations maybe? See table week 6 18:69:20. Thanks for your help!