How do I convert time 235959 to 23:59:59?

[Guest]

How do I convert time 235959 to 23:59:59 or 15959 to 1:59:59? Thanks!

 

[Douglas J Steele]

Assuming that what you're showing is in a string, you can write a little
function like

Function ConvertToTime(TextTime As String) As Variant

Select Case Len(TextTime)
Case 5
ConvertToTime = TimeSerial(Left(TextTime, 1), _
Mid(TextTime, 2, 2), Right(TextTime, 2))
Case 6
ConvertToTime = TimeSerial(Left(TextTime, 2), _
Mid(TextTime, 3, 2), Right(TextTime, 2))
Case Else
ConvertToTime = Null
End Select

End Function

If it's numeric, you could use

CDate(Format(NumericTime, "#:00:00"))

Actually, now that I think about it, if it's text, you could use:

CDate(Format(CLng(TextTime, "#:00:00")))

 

[Van T. Dinh]

Doug

Excellent idea with the last expression but I think one of the closing
panrentheses needs to be moved like:

CDate(Format(CLng(TextTime), "#\:00\:00"))

 

[Douglas J. Steele]

Right you are, Van. Good catch. See, I am only human! <g>

 

[Van T. Dinh]

I know I am definitely human: I made the SAME mistake twice in the thread
"InputMask" ...