Converting a number format into military time

  • Thread starter Thread starter jim
  • Start date Start date
J

jim

How do I convert a text number format (91245) into a
military time format (09:12:45)
Also my original data set do not always have 6 digits.

The format for the orignal data is text.
 
The first step is to convert it into a time. Then you can use the Format
function, with a format of hh:nn:ss.

It's probably best to write a little function to do the conversion.
Something like the following untested air code should work:

Function ConvertTextTime(TextTime As Variant) As Variant
' Converts strings represnting times in hnnss or hhnnss
' format to proper times.
' Input Value TextTime is a Variant, rather than a String,
' so that it can be used in queries (for those cases when
' the text field
' in question contains Null)
' Returns a Variant, rather than a Date, value so that
' it can return Null when the input is invalid.

Dim intLen As Integer

intLen = Len(TextTime & vbNullString)
Select Case intLen
Case 5
ConvertTextTime = TimeSerial( _
CInt(Left$(TextTime, 1)), _
CInt(Mid$(TextTime, 2, 2)), _
CInt(Right$(TextTime, 2)))
Case 6
ConvertTextTime = TimeSerial( _
CInt(Left$(TextTime, 2)), _
CInt(Mid$(TextTime, 3, 2)), _
CInt(Right$(TextTime, 2)))
Case Else
ConvertTextTime = Null
End Select

End Function

Note that I'm assume that the text time is in 24 hour format: that you'll
have 131500, rather than 11500. If that's not the case, how do you
distinguish between AM and PM?
 
Back
Top