Full Version: Converting Seconds, Summing
danbouton
I know this is another of those elementary things I am just missing.

In the detail section of my report I have a field [Duration]. Duration represents seconds.
In the report footer I have a the sum of Duration, or =Sum([Duration]).

I would like to add in the Detail section of the report an expression which will take [Duration] and convert it to hh/mm/ss.
I would like to add in the Report Footer section of the report an expression which will take my sum of [Duration] and convert it to hh/mm/ss.... keeping in mind, this may be over 24 hours.

Suggestions?
Here is a Link to a Microsoft Function called "On Time and How Much has Elapsed." Copy this function into your Modules area. Then in your Detail area type the name of the Function around your Field, like the following:

=HoursAndMinutes([Your Field Name]) ----Make sure you include the equals sign

The above will convert the number in Your Field to hours and minutes.

Then in the Report Footer, type the following:

=HoursAndMinutes(Sum([Your Field Name])) ----Make sure you include the equals sign

You should use the same Field as in the First formula.

Here is a link to the MS function. Hour And Minutes function
rbianco
I wanted the excercise and came up with this duration function:

CODE
Public Function MyDuration(MySeconds As Long) As String
Dim iHours As Long
Dim iMinutes As Integer
Dim iSeconds As Integer

iHours = Fix(MySeconds / 3600)
iMinutes = Fix((MySeconds - (iHours * 3600)) / 60)
iSeconds = (MySeconds - (iHours * 3600)) - (iMinutes * 60)

If iHours > 0 Then
MyDuration = CStr(iHours) & ":"
Else
MyDuration = "0:"
End If

If iMinutes > 0 Then
MyDuration = MyDuration & Right("0" & CStr(iMinutes) & ":", 3)
Else
MyDuration = MyDuration & "00:"
End If

If iSeconds > 0 Then
MyDuration = MyDuration & Right("0" & CStr(iSeconds), 2)
Else
MyDuration = MyDuration & "00"
End If
End Function
doctor9
Apparently it's a slow day here at UA. Here's my take on it:

CODE
Public Function SecsToTime(lngSeconds As Long) As String

Dim intDays As Integer, intHrs As Integer, intMins As Integer

'   60 Secs per min
'   3600 Secs per hour
'   86,400 Secs per day

intDays = Int(lngSeconds / 86400)
lngSeconds = lngSeconds - (intDays * 86400)

intHours = Int(lngSeconds / 3600)
lngSeconds = lngSeconds - (intHours * 3600)

intMins = Int(lngSeconds / 60)
lngSeconds = lngSeconds - (intMins * 60)

SecsToTime = ""

'   Convert days to hours
If intDays > 0 Then
intHours = intHours + (24 * intDays)
End If

SecsToTime = Format(intHours, "00") & "/" & Format(intMins, "00") & _
"/" & Format(lngSeconds, "00")

End Function

Hope this helps,

Dennis
theDBguy
Hi All,

Sounds like fun. Here's my "lazy" take on it:

CODE
=[Duration]\3600 & ":" & ([Duration] Mod 3600)\60 & ":" & [Duration] Mod 60

Cheers
danbouton
QUOTE (rbianco @ Apr 29 2010, 11:26 AM)
I wanted the excercise and came up with this duration function:

CODE
Public Function MyDuration(MySeconds As Long) As String
Dim iHours As Long
Dim iMinutes As Integer
Dim iSeconds As Integer

iHours = Fix(MySeconds / 3600)
iMinutes = Fix((MySeconds - (iHours * 3600)) / 60)
iSeconds = (MySeconds - (iHours * 3600)) - (iMinutes * 60)

If iHours > 0 Then
MyDuration = CStr(iHours) & ":"
Else
MyDuration = "0:"
End If

If iMinutes > 0 Then
MyDuration = MyDuration & Right("0" & CStr(iMinutes) & ":", 3)
Else
MyDuration = MyDuration & "00:"
End If

If iSeconds > 0 Then
MyDuration = MyDuration & Right("0" & CStr(iSeconds), 2)
Else
MyDuration = MyDuration & "00"
End If
End Function

I used this one. Thank you to all. I created a module and named it Mod_MyDuration. This was a great find! Thank you. This piece of code is going in my collection.
danbouton
QUOTE (theDBguy @ Apr 29 2010, 01:11 PM)
Hi All,

Sounds like fun. Here's my "lazy" take on it:

CODE
=[Duration]\3600 & ":" & ([Duration] Mod 3600)\60 & ":" & [Duration] Mod 60

Cheers

I like this one too... I just dont want to remember it all the time. It's the only reason I went with the function instead.
theDBguy
QUOTE (danbouton @ Apr 29 2010, 10:29 AM)
I like this one too... I just dont want to remember it all the time. It's the only reason I went with the function instead.

Hi,

I told you I was lazy. If I have to convert it to a function, here's what it might look like:

CODE
Public Function MyDuration(Duration As Long) As String
MyDuration = Duration\3600 & ":" & (Duration Mod 3600)\60 & ":" & Duration Mod 60
End Function

Note: I'm still doing it the lazy way - without error checking.

Cheers.
rbianco
While I "technically may have "won" I will always bow down to one liners Well done, you dastardly dbGuy.

I am so rudimentary in my approach, needing to "see" every little step occur in my code, that I currently lack the faith <read: skill > to come up with such a streamlined solution.
theDBguy
QUOTE (rbianco @ Apr 29 2010, 11:14 AM)
While I "technically may have "won" I will always bow down to one liners Well done, you dastardly dbGuy.

I am so rudimentary in my approach, needing to "see" every little step occur in my code, that I currently lack the faith <read: skill > to come up with such a streamlined solution.

Thanks, rbianco.

I don't see anything wrong with your code or Dennis'. It's easier to understand than mine. If I wasn't lazy and added the error checking and comments in my code, I may end up with a longer function than yours.

Cheers