vba 将包含小时、分钟、秒的字符串转换为十进制小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8397477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert a string containing hours, minutes, seconds to decimal hours
提问by Madi D.
I have an automatically generated Excel sheet that has a column containing string values representing time spans. The values have the following format:
我有一个自动生成的 Excel 工作表，其中有一列包含表示时间跨度的字符串值。这些值具有以下格式：
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?
我需要将它们转换为显示十进制小时数（例如 17.1 小时）。我怎样才能做到这一点？
回答by Jean-Fran?ois Corbett
You don't have to use VBA. This worksheet formula will do the trick.
您不必使用 VBA。这个工作表公式可以解决问题。
=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. VALUEthen 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.
这将删除“秒”字符串并用字符替换“小时”和“分钟”分隔符字符串:。VALUE然后将其解释为日期/时间，计算结果为一天的一小部分；因此，对于您的“17 小时 6 分 16 秒”示例，为 0.712685185 天。将此乘以 24 给出十进制小时数，即 17.1044。
To make this more robust, you could start by SUBSTITUTEing out the spaces, but the above gives you the general idea.
为了使这更健壮，您可以从SUBSTITUTE删除空格开始，但以上为您提供了总体思路。
If you must do it in VBA, then I would do it like this:
如果您必须在 VBA 中执行此操作，那么我会这样做：
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.
回答by Buddha
There may be better ways, but this is one way of doing it.
可能有更好的方法，但这是一种方法。
hours = Trim(Split(timeString, "hours,")(0))
minutes = Trim(Split(Split(timeString, "hours,")(1), " minutes,")(0))
seconds = Trim(Split(Split(Split(timeString,"hours,")(1)," minutes,")(1), "seconds")(0))
TimeInPercentage = CInt(hours) + (1/60) * (CInt(minutes)) + (1/3600) * CInt(seconds)
