Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    5

    Exclamation Unanswered: Totalling time, Can you please help?

    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

    Please help me,
    Thank you
    Last edited by sokol18; 03-21-03 at 14:29.

  2. #2
    Join Date
    Jan 2003
    Posts
    126
    It's a format issue, try it in excel, if you format the result to be: [h]:mm:ss it will work.

    HTH
    Ken

  3. #3
    Join Date
    Mar 2003
    Posts
    5
    I dont think it is a format issue

  4. #4
    Join Date
    Jan 2003
    Posts
    126
    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

  5. #5
    Join Date
    Mar 2003
    Posts
    5
    Thank you for helping,

    I hope you will find a solution with your temp db,
    but I have been trying formatting with mine, and I don't know where to change the format for the time fields... in the report i cant do it..?

  6. #6
    Join Date
    Mar 2003
    Posts
    2

    Smile Re: Totalling time, Can you please help?

    Try this:
    -create a module
    -insert this function

    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
    End Function


    Exchange Sum by Cvhm.

    Hope that helps,

    Eudes S.




    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

    Please help me,
    Thank you

  7. #7
    Join Date
    Oct 2002
    Posts
    61

    Convert Time to Minutes and use this function

    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
    Else
    Hours = (Abs([Min] \ 60))
    Minutes = ([Min] - (Hours * 60))
    End If
    hmConvert = Hours & ":" & Format(Minutes, "00")
    End Function


    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •