Convert Seconds into HH:MM:SS 1

[arryb]

I have a field in a report which displays a value by total seconds ie 1827 seconds. I would like to convert this value into a new field shown as HH:MM:SS.

Does anyone have any ideas on how i can achieve this.

All help appreciated.

Cheers

Arry

 

[koresnordic]

try the following in a sql statement
SELECT Int([seconds]/3600) AS [hour], Int(([seconds]-[hour]*3600)/60) AS [min], [seconds]-([hour]*3600)-([min]*60) AS secs, [hour] & ":" & [min] & ":" & [secs] AS [time]



Graham

 

[arryb]

Many Thanks Graham,

This has been doing my head in for quite a few hours this morning.

Have a star!!!!

 

[MichaelRed]

HMS: Format(SEcs/86400, "h:m:s&quot

? Format(MySec/86400, "h:m:s&quot
0:30:27





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[arryb]

Michael,

Sorry being a bit think. Could you explain you last post.

 

[MichaelRed]

What's to explain? There are 86400 Seconds in a day. Date/Time is represented in Ms. As ~~ a Double, with the "Whole Number" portion representing the number of days (normally taken as the number thereof since ~~? 12/30/1898 nad the fractional part representing the "percent" of any partial day. The simple division converts your number of seconds to the "percent" and the format simply provides the "traditional" string format of hours minutes and seconds. It, like the other 'soloution', will truncate any whole number (of days). The first line of my post illustrates the 'implementation' as a calculated field of a query, while the other two illustrate the 'process' in the 'immediate' window using an ;immediate' variable, to emphasize that the 'Variable' name is one I generated, not per your (actual/literal) first posting.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[arryb]

Thanks.

 

[arryb]

I am using the formula as per above but when the seconds equal over 24 hours a day is dropped. How can i get this field to show the tot HH:MM:SS and not just the HH:MM:SS of the "current day".

I.e 86412 Seconds should equal 24:00:12 but shows as 00:00:12.

All help appreciated.

 

[MichaelRed]

Public Function basSec2Day(lngSecIn As Long) As String

    'Michael Red    11/11/2003 Tek-Tips thread703-695284 for Arryb

    Const SecPerDay As Long = 86400
    Dim MySecs As Long
    Dim Idx As Integer

    MySecs = lngSecIn

    While MySecs > SecPerDay

        MySecs = MySecs - SecPerDay
        Idx = Idx + 1

    Wend

     basSec2Day = Str(Idx) & " Days and " & Format(MySecs / 86400, "h:m:s")

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places