CODEWITHSUNDEEP
excelvbadatetime
MrPatterns
MrPatterns

Reputation: 4434

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

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:

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.

Upvotes: 1

Views: 84484

Answers (3)

SeanC
SeanC

Reputation: 15923

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

Upvotes: 7

barry houdini
barry houdini

Reputation: 46411

Try TEXT function, i.e. in B1

=TEXT(A1,"00\:00\:00")+0

and format as hh:mm:ss

Upvotes: 6

alan
alan

Reputation: 4852

I can't tell if you want VBA code, or a formula for a cell, or what Custom, type 00000 is. 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.)

So assuming your source data are really strings, here's an alternative, in case you really want 'am' and 'pm' for some reason:

=MID(A1,1,2) & ":" & MID(A1,3,2) & ":" & MID(A1,5,2) & IF(VALUE(MID(A1,1,2)) < 12," (am)", " (pm)")

Upvotes: 3

Related Questions