I have a spreadsheet that I’m working on with thousands and thousands of time values listed strictly in seconds that I need to have converted into an hh:mm:ss format. Now, if we were just working in Excel, that’s as easy as saying =A1 / (1440*60) where A1 holds the value in seconds, then applying the time/37:30:55 format; a format that ensures that the value doesn’t roll over after 24 hours. The problem I had was that it all needed to be done in VBA which, apparently, doesn’t have any means of formatting a value in such a way… atleast none that I could fine in a day or so of searching…

So, just in case any one else out there is having the same issue, I thought I’d share the function that I ended up writing. It does the job quite nicely.

Function convertTime(i As String)
' D'Arcy Gregoire
' October 24, 2007
'
Dim hVal As Double
Dim hInt As Double
Dim hDec As Double
Dim hFin As String
Dim mVal As Double
Dim mInt As Integer
Dim mDec As Double
Dim mFin As String
Dim sVal As Double
Dim sInt As Integer
Dim sFin As String
'
' Hours
If i < 3600 Then
        hFin = "00"
        mVal = Evaluate(i / 60)
    Else
        hVal = Evaluate(Round(i, 0) / 3600)
        hInt = InStrRev(hVal, ".")
        If hInt = 0 Then
            hFin = "0" & Trim(Str(hVal))
        Else
            hInt = Evaluate(Left(hVal, hInt - 1))
            If hInt <= 9 Then
                hFin = "0" & Trim(Str(hInt))
            Else
                hFin = Trim(Str(hInt))
            End If
        End If
        hDec = InStrRev(hVal, ".")
        If hDec = 0 Then
            hDec = 0
        Else
            hDec = Evaluate(Mid(hVal, hDec, 99))
        End If
        mVal = Evaluate(60 * hDec)
    End If
' Minutes
    If i < 60 Then
        mFin = "00"
        sVal = i
    Else
        mInt = InStrRev(mVal, ".")
        If mInt = 0 Then
            mFin = "0" & Trim(Str(mVal))
        Else
            mInt = Evaluate(Left(mVal, mInt - 1))
            If mInt <= 9 Then
                mFin = "0" & Trim(Str(mInt))
            Else
                mFin = Trim(Str(mInt))
            End If
        End If
        mDec = InStrRev(mVal, ".")
        If mDec = 0 Then
            mDec = 0
        Else
            mDec = Evaluate(Mid(mVal, mDec, 99))
        End If
        sVal = Evaluate(60 * mDec)
    End If
' Seconds
    If sVal <= 9 Then
        sFin = "0" & Trim(Str(Evaluate(Round(sVal, 0))))
    Else
        sFin = Trim(Str(Evaluate(Round(sVal, 0))))
    End If
    ' Display result
    convertTime = hFin & ":" & mFin & ":" & sFin
End Function

Excel Solutions Excel, VBA, visual basic