I think it's a bug in Access. If you try it in excel it works just fine. However if you don't format the result cell you will get 2:00, after formatting it with [h]:mm you do in dead get 26:00:00 which is 26 hours.
I'm playing with it in a temp db when I find any answer I'll post back
Public Function CVHM(VAL) As String
Dim HR, MN, HR_COR
HR = Int(VAL * 24)
MN = Left(Trim(Str(Round((((VAL * 24) - Int(VAL * 24)) * 60), 3))), 2)
If Left(MN, 1) = "." Then MN = "0" & MN
If Eval(MN) < 10 Then MN = "0" & Left(Trim(MN), 1)
HR_COR = HR & ":" & MN
CVHM = HR_COR
Exchange Sum by Cvhm.
Hope that helps,
Originally posted by sokol18
I have used the Sum function to get the total time from various records in my report but once the hours get passed 24 they reset to 00 and need the total hours and minutes...
For example 10:00 + 10:00 + 6:00 = 2:00, but I want to have the total, which is 26:00 hours...
I don't think it's a format issue... where do I change the format if it is
Convert all time values into minutes (i.e. Hour(10:00)*60 + minute(10:00) = 600 minutes) and use regular math functions (i.e. 600 + 600 + 360 = 1560. then use the following function
Function hmConvert(Min As Variant) As String
Dim Hours, Minutes
If (Min & "") = "" Then 'If Min Variable is null it returns a null
Hours = Null
Minutes = Null
Hours = (Abs([Min] \ 60))
Minutes = ([Min] - (Hours * 60))
hmConvert = Hours & ":" & Format(Minutes, "00")
you can also subtract time using the above sample. I use this function all the time!!!
Last edited by Chumpie999typla; 03-28-03 at 22:12.