Time Function

[Chuck_S via AccessMonster.com]

Hi

I’m using the below function that I got from Microsoft’s site. It works great
for adding up several time spans and even totals times that add up to more
then 24 hours. My question is, is there a way to have this function output
the minutes as a decimal and still work correctly over 24 hours.

Thanks in advance for any help with this.



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

 

[Stefan Hoffmann]

hi Chuck,

My question is, is there a way to have this function output
the minutes as a decimal and still work correctly over 24 hours.

Why not, its just another result format.

HoursAndMinutes = Hours & ":" & Format(minutes, "00")

HoursAndMinutes = Hours + Minutes / 60

Should work.


mfG
--> stefan <--

 

[Guest]

Yes. Here is the change to do that. Sorry, but I had to clean the code up.
Although it works, it is ugly code that violates a lot of good coding
practices. Any time you see VBA code produced by Microsoft, you can pretty
much bet it is bad coding.

Public Function HoursAndMinutes(varInterval As Variant) As String
Dim lngTotalMinutes As Long
Dim lngTotalSeconds As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

If IsNull(varInterval) Then
Exit Function
End If

lngHours = Int(CSng(varInterval * 24))
lngTotalMinutes = Int(CSng(varInterval * 1440)) ' 1440 = 24 hrs * 60
mins
lngMinutes = lngTotalMinutes Mod 60
lngTotalSeconds = Int(CSng(varInterval * 86400)) ' 86400 = 1440 * 60 secs
lngSeconds = lngTotalSeconds Mod 60

If lngSeconds > 30 Then ' round up the minutes and
lngMinutes = lngMinutes + 1
End If
HoursAndMinutes = Format(lngHours + (lngMinutes / 60), "#0.00")

End Function

 

[Chuck_S via AccessMonster.com]

Thanks!!! it works great.

Yes. Here is the change to do that. Sorry, but I had to clean the code up.
Although it works, it is ugly code that violates a lot of good coding
practices. Any time you see VBA code produced by Microsoft, you can pretty
much bet it is bad coding.

Public Function HoursAndMinutes(varInterval As Variant) As String
Dim lngTotalMinutes As Long
Dim lngTotalSeconds As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

If IsNull(varInterval) Then
Exit Function
End If

lngHours = Int(CSng(varInterval * 24))
lngTotalMinutes = Int(CSng(varInterval * 1440)) ' 1440 = 24 hrs * 60
mins
lngMinutes = lngTotalMinutes Mod 60
lngTotalSeconds = Int(CSng(varInterval * 86400)) ' 86400 = 1440 * 60 secs
lngSeconds = lngTotalSeconds Mod 60

If lngSeconds > 30 Then ' round up the minutes and
lngMinutes = lngMinutes + 1
End If
HoursAndMinutes = Format(lngHours + (lngMinutes / 60), "#0.00")

End Function

Hi

[quoted text clipped - 26 lines]

End Function