如何在 VBA 中将给定数字转换为时间格式 HH:MM:SS？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11087787/
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 convert a given number into time format HH:MM:SS in VBA?
提问by phan
I received a list of numbers in Custom format (Type: 000000) that represent military time. It always contain 6 digits, and leading zeros are used when necessary. For example:
我收到了代表军用时间的自定义格式（类型：000000）的数字列表。它总是包含 6 位数字，必要时使用前导零。例如：
- 123456 becomes 12:34:56 (pm)
- 000321 becomes 00:03:21 (am)
- 142321 becomes 14:23:21 (pm)
- 123456 变为 12:34:56 (pm)
- 000321 变为 00:03:21（上午）
- 142321 变为 14:23:21（下午）
How do I convert the integers in Column A into the format in the Column B (hh:mm:ss)? I'd like to use military, 24-hr clock.
如何将 A 列中的整数转换为 B 列中的格式 (hh:mm:ss)？我想使用军用的 24 小时制时钟。
回答by SeanC
assuming its a real integer,and not text:
假设它是一个实数，而不是文本：
=TIME(INT(A1/10000),INT(MOD(A1,10000)/100),MOD(A1,100))
=TIME(INT(A1/10000),INT(MOD(A1,10000)/100),MOD(A1,100))
if it is text, then use
如果是文本，则使用
=TIME(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(RIGHT(A1,2)))
=TIME(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(RIGHT(A1,2)))
format the result however you want.
根据需要格式化结果。
As a VBA Function: Integer:
作为 VBA 函数：整数：
Function RetTime(IntTime As Long) As Date
RetTime = TimeSerial(Int(IntTime / 10000), Int((IntTime Mod 10000) / 100), (IntTime Mod 100))
End Function
String:
细绳：
Function RetTimeS(StrTime As String) As Date
RetTimeS = TimeSerial(Val(Left(StrTime, 2)), Val(Mid(StrTime, 3, 2)), Val(Right(StrTime, 2)))
End Function
回答by barry houdini
Try TEXT function, i.e. in B1
尝试 TEXT 函数，即在 B1 中
=TEXT(A1,"00\:00\:00")+0
=TEXT(A1,"00\:00\:00")+0
and format as hh:mm:ss
并格式为 hh:mm:ss
回答by alan
I can't tell if you want VBA code, or a formula for a cell, or what Custom, type 00000is. Sean's answer above is probably the best from the standpoint of using VBA functions properly, but I don't believe there's any way to format a time object in military time with "(am)" and "(pm)" tacked on to the end. At least not using standard Excel formatting. (Probably because am and pm are redundant in military time.)
我不知道你是想要 VBA 代码，还是单元格的公式，或者是什么Custom, type 00000。从正确使用 VBA 函数的角度来看，上述肖恩的回答可能是最好的，但我不相信有任何方法可以在军事时间格式化时间对象，并将“（am）”和“（pm）”添加到末尾. 至少不使用标准的 Excel 格式。（可能是因为 am 和 pm 在军用时间是多余的。）
So assuming your source data are really strings, here's an alternative, in case you really want 'am' and 'pm' for some reason:
因此，假设您的源数据确实是字符串，这里有一个替代方案，以防您出于某种原因确实需要 'am' 和 'pm'：
=MID(A1,1,2) & ":" & MID(A1,3,2) & ":" & MID(A1,5,2) & IF(VALUE(MID(A1,1,2)) < 12," (am)", " (pm)")
