Problem converting seconds to hours and minutes

[Liz_I3]

Hi
I have a query that sums a the seconds for a user over a time period. I want want the sum of the total seconds to be in Hours and mininuts. I have tried on the query grid to create a new field called duration with the below formula but with 388466 seconds it returns 11:54:06 it should be 107:54:06 (The 06 seconds is optional, rounding up the minutes would be fine). What am I missing?

Duration: Format([SumOfSeconds]/86400,"hh:nn:ss")

Thanks
L

 

[baitmaster]

try [h]:mm:ss instead

 

[shg]

Hi, Liz.

That would be easy using Excel's Text function, but I don't see that Format supports the [h] formatting code. Maybe a function:

Sub demo()
  MsgBox hms(388466 / 86400#)
End Sub

Function hms(t As Date) As String
  hms = Int(t * 24) & Format(t, ":nn:ss")
End Function

 

[Liz_I3]

Thank you for trying to help me, but I think this issue is that my raw data is duplicating the data, therefore when I sum my seconds I am getting more seconds that can be in one day, my user can't work 107 hrs a day LOL
I have ask the IT dept to change the format of the data dump they supplied me. The formula should work correctly once I have the correct data

Thanks again for your help

L

 

[shg]

Can't help at all with Access, Liz. Good luck.

 

[apr pillai]

Use this Function:

Public Function FormatTime(ByVal TimeInSeconds As Long) As String
  Dim h As Long, m As Long, s As Long
  h = Int(TimeInSeconds / 3600)
  m = Int((TimeInSeconds - h * 3600) / 60)
  s = TimeInSeconds - ((h * 3600) + (m * 60))
  FormatTime = h & ":" & m & ":" & s
End Function