Welcome to OGeek Q&A 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 Reply

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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...