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.