UtterAccess Discussion Forums > Converting Seconds, Summing

danbouton

Apr 29 2010, 09:41 AM

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?

RAZMaddaz

Apr 29 2010, 10:20 AM

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

Apr 29 2010, 10: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

doctor9

Apr 29 2010, 10:28 AM

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

Apr 29 2010, 12: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

danbouton

Apr 29 2010, 12:28 PM

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

Apr 29 2010, 12:29 PM

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.

RAZMaddaz

Apr 29 2010, 12:31 PM

theDBguy

Apr 29 2010, 12:54 PM

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

Apr 29 2010, 01:14 PM

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

Apr 29 2010, 01:22 PM

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