excel - How do I convert a given number into time format HH:MM:SS in VBA?

Question

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.

Answer 1

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