Elapsed Time Assistance

[Guest]

I found the following article and sample DB.
http://office.microsoft.com/en-au/assistance/HA011102181033.aspx

After importing the module, everything works great. However instead of just
House:Minutes, I would like to edit the module below to display
Hours:Minutes:Seconds

Could someone be so kind as to show me what to change below.

Thanks,
Hank

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60
totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours
HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

 

[Duane Hookom]

Consider using Doug Steele's function found at
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[Guest]

Thanks.

After a bit of playing, I got what I wanted by using the following:

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60
totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

HoursAndMinutes = hours & ":" & Format(minutes, "00") & ":" & Format(seconds, "00")

End Function

Hank

 

[Marshall Barton]

I found the following article and sample DB.
http://office.microsoft.com/en-au/assistance/HA011102181033.aspx

After importing the module, everything works great. However instead of just
House:Minutes, I would like to edit the module below to display
Hours:Minutes:Seconds

Could someone be so kind as to show me what to change below.


Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60
totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours
HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

I think this will do that:

Public Function HoursAndMinutes(interval As Variant) _
As String
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))
totalminutes = CLng(interval * 24 * 60)
minutes = totalminutes Mod 60
totalseconds = CLng(interval * 24 * 60 * 60)
seconds = totalseconds Mod 60

HoursAndMinutes = hours & ":" & Format(minutes, "00") _
& ":" & Format(seconds, "00")
End Function