4 Replies Latest reply on Sep 26, 2019 9:49 AM by sara vana

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

    raunak jaiswal

      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