VBA 转换时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14010581/
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
VBA convert time
提问by user1888663
I need to convert time from 14:54 to a double 14+54/60. And i do this with the following code:
我需要将时间从 14:54 转换为双 14+54/60。我使用以下代码执行此操作：
Private Function Omzetten(ByVal time As String) As Double
Dim min As Integer, hours As Integer, datum As Date
datum = CDate(time)
min = DatePart("n", datum)
hours = DatePart("h", datum)
Omzetten = hours + min / 60
End Function
But when the time is 26:00 he only gives 2 because 26-24 is 2. So I thought to ad day = DatePart("d", datum), but then he always gives day = 30. Does anyone has a solution?
但是当时间是 26:00 时，他只给 2，因为 26-24 是 2。所以我想广告 day = DatePart("d", datum)，但后来他总是给 day = 30。有人有解决方案吗？
回答by PeterJ
Try the following, I used VB.Net which from above looks like it must be compatible with the newer VBA variants:
尝试以下操作，我使用了 VB.Net，从上面看起来它必须与较新的 VBA 变体兼容：
Private Function Omzetten(ByVal time As String) As Double
Dim Hours As Integer = CInt(time.Substring(0, time.IndexOf(":")))
Dim Minutes As Integer = CInt(time.Substring(time.IndexOf(":") + 1))
Return Hours + Minutes / 60
End Function
Just as a note you might want to add some extra checks, the above code will for example fail on non-numeric input or if the time doesn't contain a colon. You might want something more like the following to cope with varying inputs:
就像您可能想要添加一些额外检查一样，上面的代码将在非数字输入或时间不包含冒号时失败。您可能需要更像以下内容来处理不同的输入：
Private Function Omzetten(ByVal time As String) As Double
Dim Hours As Integer = 0
Dim Minutes As Integer = 0
Dim HoursStr As String
If time.IndexOf(":") = -1 Then
HoursStr = time
Else
HoursStr = time.Substring(0, time.IndexOf(":"))
End If
If IsNumeric(HoursStr) Then
Hours = CInt(HoursStr)
End If
If time.IndexOf(":") >= 0 Then
Dim MinutesStr As String = time.Substring(time.IndexOf(":") + 1)
If IsNumeric(MinutesStr) Then
Minutes = CInt(MinutesStr)
End If
End If
Return Hours + Minutes / 60
End Function
回答by InContext
if its always in the format hours : minsthen use the below:
如果它总是在格式hours : mins然后使用以下：
Dim str As String
Dim strSplit() As String
Dim Val As Double
str = "26:00"
strSplit = Split(str, ":")
Val = CInt(strSplit(0)) + CInt(strSplit(1)) / 60
回答by Alex P
I think you can achieve this with basic Excel formulas.
我认为您可以使用基本的 Excel 公式来实现这一点。
As times are stored as numbers if you divide any time by 1/24 (i.e. an hour) you'll get the answer as a double.
由于时间存储为数字，如果您将任何时间除以 1/24（即一小时），您将得到双倍的答案。
Note- if you want to use times over 24 hrs (e.g. 26:00) then set the cell custom format to [h]:mm:ss
注意- 如果您想使用超过 24 小时（例如 26:00）的时间，则将单元格自定义格式设置为[h]:mm:ss
Examples
例子
A B
1 14:54 =A1/(1/24) // = 14.9
2 26:00 =A1/(1/24) // = 26.0
Does this help?
这有帮助吗？
