D’ArcyGregoire.com

Oct 24 2007

Convert time in seconds to time format

Published by D'Arcy Gregoire at 5:41 pm under Excel Solutions

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