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,548
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,442
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,541
Messages
5,341,061
Members
399,414
Latest member
EMW2159

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top