如何使用 VBA 中的 format() 函数以 [h]:mm excel 格式格式化时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11143746/
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
How do I format Time in [h]:mm excel format using format() function in VBA
提问by Sam
Is it possible to format time in [h]:mm format using VBA?
是否可以使用 VBA 以 [h]:mm 格式格式化时间？
[h]:mm format in excel would show 25 hours as 25:00, 125 hours as 125:00
[h]:mm 格式在excel中将25小时显示为25:00，125小时显示为125:00
I've tried several things such as:
我尝试了几件事，例如：
format(datetime, "[h]:mm")
format(datetime, "H:mm")
format(datetime, "hh:mm")
None of these have the desired effect. I've also had a look through the MS help and can't find anything useful.
这些都没有达到预期的效果。我还查看了 MS 帮助，但找不到任何有用的信息。
回答by Joel Spolsky
Use the TEXT worksheet function via the application object, as so:
通过应用程序对象使用 TEXT 工作表函数，如下所示：
x = Application.Text(.294,"[h]:mm")
回答by Tim Williams
JFC beat me to it, but here's what I came up with anyway...
JFC 击败了我，但无论如何，这就是我想出的……
Sub Test()
Debug.Print FormatHM(24 / 24) '24:00
Debug.Print FormatHM(25 / 24) '25:00
Debug.Print FormatHM(48 / 24) '48:00
Debug.Print FormatHM(48.6 / 24) '48:36
End Sub
Function FormatHM(v As Double) As String
FormatHM = Format(Application.Floor(v * 24, 1), "00") & _
":" & Format((v * 1440) Mod 60, "00")
End Function
回答by SeanC
not with the format function, but you can use Range(MyData).NumberFormat = "[h]:mm"
不使用格式功能，但您可以使用 Range(MyData).NumberFormat = "[h]:mm"
回答by Jean-Fran?ois Corbett
It looks like VBA's Formatfunction has no built-in way of doing this. (Eyes rolling.)
看起来 VBA 的Format函数没有内置的方法来做到这一点。（翻白眼。）
This home-made function will do the trick:
这个自制函数可以解决问题：
Function HoursAndMinutes(datetime As Date) As String
Dim hours As Integer
Dim minutes As Integer
hours = Int(datetime) * 24 + Hour(datetime) ' the unit of Date type is 1 day
minutes = Round((datetime * 24 - hours) * 60)
HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function
Usage:
用法：
Dim datetime As Date
datetime = TimeSerial(125, 9, 0) ' 125 hours and 9 minutes
Debug.Print HoursAndMinutes(datetime) ' 125:09
