Results 1 to 5 of 5

Thread: Convert HHMMSS to time in VBA?

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Convert HHMMSS to time in VBA?

    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

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,427
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Convert HHMMSS to time in VBA?

    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 by joeu2004; Sep 9th, 2019 at 04:17 PM.

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert HHMMSS to time in VBA?

    Quote Originally Posted by joeu2004 View Post
    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

    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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,207
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Convert HHMMSS to time in VBA?

    Quote Originally Posted by JumboCactuar View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Nov 2016
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert HHMMSS to time in VBA?

    Thankyou @Rick Rothstein

    This will be faster

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •