How to convert call duration to time in Excel

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
We export the call detail report to find out how much time is being spent on the phone for various departments. The exported call duration field is a text field with hours, minutes and seconds and there is no easy way to convert the text to a time value.

This text can be converted to a time value in Excel using Visual Basic (VB) script.

Start by exporting the report and opening it in Excel. Once opened, open the VB window using ALT+F11. Right click the name of your worksheet in the upper left hand window [e.g., VBAProject (Book1)"] and select Insert / Module. Click inside the new window on the right.

Paste the following code to create a function that will be used to convert the text:
Function PARSETIME(ByVal value As String)
    Dim arr, str As Variant
    Dim h, m, s As Integer
    arr = Split(value)
    h = m = s = 0
    For Each str In arr
        If Right(str, 1) = "h" Then
            h = Val(Left(str, Len(str) - 1))
        ElseIf Right(str, 1) = "m" Then
            m = Val(Left(str, Len(str) - 1))
        ElseIf Right(str, 1) = "s" Then
            s = Val(Left(str, Len(str) - 1))
        End If
    PARSETIME = TimeSerial(h, m, s)
End Function
Use this like any other spreadsheet function; enter "=parsetime(a2)" into the cell. This will give you a serial number that can be converted to minutes by multiplying it by 1440 (the number of minutes in a day).

I did find the following post here which is similar: How to convert Call logs export to usable data

Hope someone finds this helpful!
Photo of Mark Clifford

Mark Clifford

  • 120 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Mike

Mike, Official Rep

  • 94,980 Points 50k badge 2x thumb
Thanks for sharing. I'm sure this will help some somebody!

Photo of Bill Poirier

Bill Poirier

  • 120 Points 100 badge 2x thumb
I've tried this and get 0 as the answer. For sure it's user error, however, the time is coming out of RC as 00:01:12 (1 minute 12 seconds)...when I use the =parstime function and that cell, it returns 0. Does the data have to be formatted before using the parstime function ??
Photo of Mark Clifford

Mark Clifford

  • 120 Points 100 badge 2x thumb
At the time, the call duration looked like "1h 14m 42s" which is why the function was needed. To get the time in seconds for the current call duration format ("00:01:12") use the formula: =HOUR(G2)*3600+MINUTE(G2)*60+SECOND(G2)
This assumes the call duration is in the G column, row 2.