vba Excel：自动格式化时间的宏（无日期）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2292117/
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
Excel: macro to auto-format time (without date)
提问by MPelletier
I'm looking for a simple macro to specify cells as being of type "time" and typing only numbers (for example "955") will format that (and recognise as) "hh:mm" time. In other words, by typing "955", the cell would interpret to "09:55", whereas all I can have it do right now is interpret to "1902-08-12 00:00:00" (most likely 955 is the day number for 1902-08-12).
我正在寻找一个简单的宏来将单元格指定为“时间”类型，并且只输入数字（例如“955”）将格式化（并识别为）“hh:mm”时间。换句话说，通过输入“955”，单元格将解释为“09:55”，而我现在所能做的就是解释为“1902-08-12 00:00:00”（很可能 955 是1902-08-12 的天数）。
EDIT:
编辑：
Part of the need for this is to allow calculation of times post typing (for example, substract two time values to get a timespan, or add several timespans to get a total).
这样做的部分需要是允许计算键入后的时间（例如，减去两个时间值以获得时间跨度，或添加多个时间跨度以获得总数）。
采纳答案by Stewbob
VBA code:
VBA代码：
Dim val As String
val = myCell.Value
Dim newval As String
If Len(val) = 2 Then
newval = "00:" & val
ElseIf Len(val) = 3 Then
newval = "0" & Left(val, 1) & ":" & Right(val, 2)
ElseIf Len(val) = 4 Then
newval = Left(val, 2) & ":" & Right(val, 2)
Else
newval = "Not a valid time"
End If
myCell.Value = newval
This code does not detect if the last two digits are a valid time (greater than 59 minutes), but otherwise it should handle most cases.
此代码不检测最后两位数字是否为有效时间（大于 59 分钟），但除此之外它应该可以处理大多数情况。
You'll also need to add a case if someone types in 1 digit, ie. 1 thru 9 minutes after midnight.
如果有人输入 1 位数字，您还需要添加一个案例，即。午夜后 1 到 9 分钟。
If you want it to be formatted as an actual time (date-time type), then change the formatting of the cell to hh:mm. The value typed in, for example 955, must be manipulated to produce a fraction of a day.
如果您希望将其格式化为实际时间（日期时间类型），则将单元格的格式更改为 hh:mm。输入的值（例如 955）必须经过处理才能生成一天的一小部分。
pseudocode:
伪代码：
(left(myCell,len(myCell)-2) + (right(myCell,2)/60)) / 24
This produces the proper decimal value for how much of the day has elapsed and thus will display as a proper 'Time' in the cell with hh:mm formatting.
这会为一天过去的时间生成正确的十进制值，因此将在单元格中以 hh:mm 格式显示为正确的“时间”。
回答by guitarthrower
This isn't the VBA, but a formula that will change the formatting. You could incorporate this into VBA if you wanted to, but should help as a jumping point.
这不是 VBA，而是一个会改变格式的公式。如果你愿意，你可以将它合并到 VBA 中，但应该作为一个跳跃点。
=TIMEVALUE(IF(LEN(A5)=3,"0"&LEFT(A5,1)&":",LEFT(A5,2)&":")&RIGHT(A5,2))
(If A5 is the cell where you enter 955)
（如果 A5 是您输入 955 的单元格）
(Also, make sure to format the formula cell as your desired time formatting.)
（此外，请确保将公式单元格格式化为所需的时间格式。）
I can help with the code if needed. Just post back.
如果需要，我可以帮助编写代码。回帖就好了
回答by z-boss
For some reason Excel does not allow to use ":" in your custom format.
But if you OK with another delimiter, say dash "-", then you can simply create a custom format like this: ##-##
Of course your time has to be in 24 hours format.
出于某种原因，Excel 不允许在您的自定义格式中使用“:”。但是，如果您可以使用另一个分隔符，比如破折号“-”，那么您可以简单地创建这样的自定义格式：##-##
当然，您的时间必须采用 24 小时格式。
Alternatively, you may first enter all your times just like numbers (or better as text if you don't want to lose trailing zeros). And then run your script to insert semicolons between hours and minutes.
或者，您可以首先像数字一样输入所有时间（如果不想丢失尾随零，则最好输入文本）。然后运行您的脚本以在小时和分钟之间插入分号。
