Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

excel - 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:

  • 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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...