5 Replies Latest reply on Feb 22, 2020 4:08 AM by Jasper Kunst

# Time format conversion from seconds to HH:MM:SS in a calculation

Hi Viz Masters,

I am trying to do some time calculation wherein i have a field called 'Total Active Agent Time' it is already in seconds and is used to calculate 'Total Handle Time' also 'Initial Time in Queue' which is also in seconds and is used to 'Total Queue Time'

Below are the formula that I am creating:

Total Handle Time:- IF  (([Start Date1])>=[Period Beginning] OR ([End Date1])<[Period Ending])AND([Total Agent Lines]+[Total Customer Lines])>0 THEN [Total Active Agent Time]/86400  END

Total Queue Time:- (SUM([Initial Time in Queue])/86400)+[RunOut Time]

Total Conversation Duration:- SUM(IF  (([Start Date1])>=[Period Beginning] OR ([End Date1])<[Period Ending])AND([Total Agent Lines]+[Total Customer Lines])>0 THEN [Total Active Agent Time]/86400  END)+(SUM([Initial Time in Queue])/86400)+[RunOut Time]

By dividing these with 86400 I assume that I am getting the values in Day(which is a floating value)

But when I am converting the format of the measures to HH:MM:SS the values are not coming correct it seems off.

If the Grand Total of Total Active Agent Time= 250,310 secs Then in days it is 250310/86400= 2.897 days. But when I am converting the format to HH:MM:SS it is coming as 21:31:50 instead it should come as near to 69:33:00. And similarly for other metrics.

I have attached the workbook that I am working on also I need to include a field in the Date Difference(it will be summed) in the Total Conversation Duration Calculation, which is in Total Conversation Duration sheet.

Please refer to Sheet 3 in the workbook.

Can anyone help me in this?

Raunak Jaiswal

• ###### 1. Re: Time format conversion from seconds to HH:MM:SS in a calculation

Don Wise

Hi Don,

I tried the solution that you provided Seconds to hh:mm:ss calc but this solution is not working for me in converting the Seconds to hh:mm:ss format.

Raunak

• ###### 2. Re: Time format conversion from seconds to HH:MM:SS in a calculation

Hello,

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

• ###### 3. Re: Time format conversion from seconds to HH:MM:SS in a calculation

Thanks a lot Don.

The solution that you provided helped me.

Regards,

Raunak

• ###### 4. Re: Time format conversion from seconds to HH:MM:SS in a calculation

Thanks, it is working good for me.

• ###### 5. Re: Time format conversion from seconds to HH:MM:SS in a calculation

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! 