如何将一个给定的数字转换成时间格式HH:MM:SS在VBA? [英] How do I convert a given number into time format HH:MM:SS in VBA?
问题描述
我收到代表军事时间的自定义格式(类型:000000)的号码列表。它总是包含6位数字,必要时使用前导零。例如:
- 123456成为12:34:56(pm)
- 000321变为00: 03:21(am)
- 142321成为14:23:21(pm)
如何将列A中的整数转换为列B(hh:mm:ss)中的格式?我想使用军事的24小时钟。
假设它是一个真正的整数,而不是text: p>
= TIME(INT(A1 / 10000),INT(MOD(A1,10000)/ 100),MOD(A1,100) code>
如果是文本,则使用
= TIME (VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(RIGHT(A1,2)))
格式化您想要的结果。
作为VBA函数:整数:
<$ p $函数RetTime(IntTime As Long)As Date
RetTime = TimeSerial(Int(IntTime / 10000),Int((IntTime Mod 10000)/ 100),(IntTime Mod 100))
结束功能
字符串:
函数RetTimeS(StrTime As String)As Date
RetTimeS = TimeSerial(Val(Left(StrTime,2)),Val(Mid(StrTime,3,2)),Val (右(StrTime,2)))
结束函数
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:
- 123456 becomes 12:34:56 (pm)
- 000321 becomes 00:03:21 (am)
- 142321 becomes 14:23:21 (pm)
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.
assuming its a real integer,and not text:
=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)))
format the result however you want.
As a VBA Function: Integer:
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
这篇关于如何将一个给定的数字转换成时间格式HH:MM:SS在VBA?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!