3 Replies Latest reply on Jan 15, 2019 12:28 AM by raunak jaiswal

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

    raunak jaiswal
    raunak jaiswal Jan 4, 2019 6:07 AM

      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?

       

      Thanks in Advance

      Raunak Jaiswal

        • 1. Re: Time format conversion from seconds to HH:MM:SS in a calculation
          raunak jaiswal
          raunak jaiswal Jan 7, 2019 4:57 AM (in response to raunak jaiswal)

          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.

           

          Can you please help me out? I have attached the workbook in the above thread along with description.

           

          Thanks in Advance!

           

          Raunak

          • 2. Re: Time format conversion from seconds to HH:MM:SS in a calculation
            Don Wise
            Don Wise Jan 14, 2019 1:56 PM (in response to raunak jaiswal)

            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.

            Screen Shot 2019-01-14 at 1.55.43 PM.png

             

            Hope it helps! Thx, Don

            • 3. Re: Time format conversion from seconds to HH:MM:SS in a calculation
              raunak jaiswal
              raunak jaiswal Jan 15, 2019 12:28 AM (in response to Don Wise)

              Thanks a lot Don.

              The solution that you provided helped me.

               

              Regards,

              Raunak