Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...