vba - Convert a string containing hours, minutes, seconds to decimal hours

Question

I have an automatically generated Excel sheet that has a column containing string values representing time spans. The values have the following format:

17 hours, 6 minutes, 16 seconds

I need to convert them to show decimal hours (e.g 17.1 hours). How can I do that?

Answer 1

You don't have to use VBA. This worksheet formula will do the trick.

=24*VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4," hours, ",":"),
     " minutes, ",":")," seconds",""))

This deletes the "seconds" string and replaces the "hours" and "minutes" delimiter strings with the : character. VALUE then interprets this as a date/time, which evaluates to a fraction of a day; so for your "17 hours, 6 minutes, 16 seconds" example, 0.712685185 days. Multiplying this by 24 gives decimal hours, i.e. 17.1044.

To make this more robust, you could start by SUBSTITUTEing out the spaces, but the above gives you the general idea.

If you must do it in VBA, then I would do it like this:

Dim myTimeString As String
Dim splitTime() As String
Dim decimalHours As Double

myTimeString = "17 hours, 6 minutes, 16 seconds"

' Remove useless characters
myTimeString = Trim(Replace(Replace(Replace(myTimeString, " ", ""), _
    ",", ""), "seconds", ""))
' Replace "hours" and "minutes" by a useful delimiter, ":"
myTimeString = Replace(Replace(myTimeString, "minutes", ":"), "hours", ":")
' String now looks like this: "17:6:16". Now split it:
splitTime = Split(myTimeString, ":")

decimalHours = CInt(splitTime(0)) + CInt(splitTime(1)) / 60 + _
    CInt(splitTime(2)) / 3600


' Alternatively, convert the string to a date, then to decimal hours
Dim myDate As Date
myDate = CDate(myTimeString)
decimalHours2 = 24 * CDbl(myDate) ' same result.