Convert HHMMSS to time in VBA?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
609
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,531
Office Version
2010
Platform
Windows
Rich (BB code):
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).
 
Last edited:

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
609
Rich (BB code):
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

Rich (BB code):
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,425
Office Version
2010
Platform
Windows
i put this together and its working

Code:
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...
Code:
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
 

Forum statistics

Threads
1,078,273
Messages
5,339,190
Members
399,288
Latest member
ossa

Some videos you may like

This Week's Hot Topics

Top