Convert seconds to DD:HH:MM:SS

[rockcliff15]

Hi,

I've got a report showing a list of durations of time in seconds and I want to be display them in DD:HH:MM:SS format.

Any ideas?



Thanks

[RuralGuy]

What have you tried?

[randman1]

There's no built-in function that I'm aware of but you could use this function in a standard module so it can be used anywhere in your app.

Public Function Sec2DHMS(lSec As Long) As String
    Dim iMin As Integer
    Dim iHour As Integer
    Dim iDay As Integer
    
    Const SecInMin As Long = 60
    Const SecInHour As Long = SecInMin * 60
    Const SecInDay As Long = SecInHour * 24
           
    iDay = Int(lSec / SecInDay)
    lSec = lSec - (iDay * SecInDay)
    iHour = Int(lSec / SecInHour)
    lSec = lSec - (iHour * SecInHour)
    iMin = Int(lSec / SecInMin)
    lSec = lSec - (iMin * SecInMin)
    
    Sec2DHMS = Format(iDay, "00") & ":" & Format(iHour, "00") & ":" & Format(iMin, "00") & ":" & Format(lSec, "00")
    
End Function

[rockcliff15]

What have you tried?

I've tried [myValue]/86400 but this just gives me a decimal of number of days and I dont know how to format this as DD:HH:MM:SSS

If it makes it any easier I could live with it being in HH:MM:SS as long as HH could go above 24 and not reset to 00.

Thanks

[RuralGuy]

Did you try the suggestion randman1 posted yesterday morning?

[rockcliff15]

There's no built-in function that I'm aware of but you could use this function in a standard module so it can be used anywhere in your app.

Public Function Sec2DHMS(lSec As Long) As String
    Dim iMin As Integer
    Dim iHour As Integer
    Dim iDay As Integer
    
    Const SecInMin As Long = 60
    Const SecInHour As Long = SecInMin * 60
    Const SecInDay As Long = SecInHour * 24
           
    iDay = Int(lSec / SecInDay)
    lSec = lSec - (iDay * SecInDay)
    iHour = Int(lSec / SecInHour)
    lSec = lSec - (iHour * SecInHour)
    iMin = Int(lSec / SecInMin)
    lSec = lSec - (iMin * SecInMin)
    
    Sec2DHMS = Format(iDay, "00") & ":" & Format(iHour, "00") & ":" & Format(iMin, "00") & ":" & Format(lSec, "00")
    
End Function

Just tried this and its perfect!

Thanks

[RuralGuy]

Glad to hear you got it sorted.