excel text to time

Question

I have an excel table with the time formatted with text like the example below. I need to convert the time from a text string into a usable format. I would be happy if I could get it all in the same format I.E. everything in seconds and work from there. I know I could do this using a helper column and a very long ugly IF left, right, mid formula and divide everything but I am working with thousands of these daily and would love to automate it better with a dynamic formula or VBscript that made these easier to work with.

     A         B
1 Server(A)  15d 3h 39m
2 Server(E)  3h 36m 44s
3 Server(C)  4m 3s
4 Server(B)  44s

Answer 1

This isn't elegant, but it would work as a UDF as long as your format is as described:

Public Function timeStringToSeconds(strIn As String) As Long
    Dim values
    values = Split(strIn, " ")
    For Each v In values
        Select Case Right$(v, 1)
            Case "d"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1)) * 86400
            Case "h"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1)) * 3600
            Case "m"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1)) * 60
            Case "s"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1))
        End Select
    Next
End Function

You could use it simply by doing this: in C1 for example: timeStringToSeconds(B1) Or run it on a range by doing something like this: range.value = timeStringToSeconds(range.value)