Convert HHMMSS to time in VBA?

JumboCactuar

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

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,634
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
662
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,665
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,355
Messages
5,413,968
Members
403,511
Latest member
Emmanuel John

This Week's Hot Topics

Top