# How to convert call duration to time in Excel

• 0
• Question
• Updated 2 years ago
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    Next    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!
• 120 Points Posted 3 years ago

• 0

Mike, Official Rep

• 93,798 Points Thanks for sharing. I'm sure this will help some somebody!

Mike
(Edited)
• 120 Points 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 ??
• 120 Points 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.