Convert HHMMSS to time in VBA?

[JumboCactuar]

Hi,
In a database our timevalues are stored like
94500 = 09:45:00
121755 = 12:17:55
58 = 00:00:58

What is the best way to convert these when pulling them via ODBC? Ideally in the SQL query but power query is an option also.

Thanks

 

[joeu2004]

Sub doit()
Dim x As Long
x = 94500
MsgBox Format(x, "00\:00\:00")
x = 121755
MsgBox Format(x, "00\:00\:00")
x = 58
MsgBox Format(x, "00\:00\:00")
End Sub

The type of "x" does not matter (Long, Double, Variant), as long as it can handle large numbers (i.e. not Integer).

 

[JumboCactuar]

Sub doit()
Dim x As Long
x = 94500
MsgBox Format(x, "00\:00\:00")
x = 121755
MsgBox Format(x, "00\:00\:00")
x = 58
MsgBox Format(x, "00\:00\:00")
End Sub

The type of "x" does not matter (Long, Double, Variant), as long as it can handle large numbers (i.e. not Integer).

thankyou,
i put this together and its working

Sub TimeConvert()
    
    Line = 2
    
    Do Until Cells(Line, 1).Value = ""
        Cells(Line, 1).Value = Format(Cells(Line, 1).Value, "00\:00\:00")
        Line = Line + 1
    Loop


End Sub

 

[Rick Rothstein]

i put this together and its working

Sub TimeConvert()
    
    Line = 2
    
    Do Until Cells(Line, 1).Value = ""
        Cells(Line, 1).Value = Format(Cells(Line, 1).Value, "00\:00\:00")
        Line = Line + 1
    Loop

End Sub

You could also do it without using a loop like this...

Sub TimeConvert()
  Dim Addr As String
  Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(@="""","""",TEXT(@,""00\:00\:00""))", "@", Addr))
End Sub

 

[JumboCactuar]

Thankyou @Rick Rothstein

This will be faster