Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to I add up hours/min (Access 97)

    I have a db where I am entering total time run for each record. When I display the records it shows the correct totals.... 2:32,,19:13. THis field is called expr2.

    I have a report were I am trying to grab the records by date and add up the totoal number of hours for each date. The hours displayed for each record are correct by when I try to add them up the totals are wrong. I might have a total of 54 hrs 23min.(manual add) and the report show 5hr22min. I use the formula =sum([expr2]) to add them up.

    with this information can anyone show me how to add hours and min. from a large numbe of records. I think it has to do with the number of hours in a day (24).

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to I add up hours/min (Access 97)

    I have done a similar task and it works correctly to add employee time for payroll. Since you haven't gotten an reply yet, I'll offer mine, but am not at all certain it is efficient. Here is the method. First I use DateDiff to get the total time in minutes at the record level. When I want the total for all the records, I use Sum([Minutes] and call it TotalMinutes. Then I use Int([TotalMinutes]/60) to get the hours. Then I use [TotalMinutes] Mod 60 to get the remaining minutes.

    If there is a better way, I'd also like to know what it is. ( By the way, given the 24 hours in a day, I don't understand why your manual sum and calculated sum come up with those numbers.)

  3. #3
    DaveShmave
    Guest

    Re: How to I add up hours/min (Access 97)

    Tom -

    "( By the way, given the 24 hours in a day, I don't understand why your manual sum and calculated sum come up with those numbers.)"

    Because when the clock gets to 24 (2400) it rolls back over and starts counting all over again (from 0100). Therefore, you really can't calculate time in the manner that Dan's trying to do it.


    Dan -

    I've had this problem and have seen solutions (or at least work arounds) before and when I locate it I'll come back and post what I find. Although Tom's solution does the job. For another, slightly different, approach:

    Change your time field to minutes (:1440 total in a day ~ 24 hours in a day * 60 minutes per hour) and divide your total minutes by 60. You're answer will be in hours. So 17.35 hours = 17 hours and 21 minutes.

    HTH

    Dave

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to I add up hours/min (Access 97)

    Your problem is that when you add time you get days and hours and you are only dislpaying hours. 54 hours and 23 minutes is two days, 5 hours and 23 minutes. When you display that as a time value you get just 5 hours and 23 minutes. I'm not an Access programmer, so I don't know the proper method of handeling that in Access. In Excel you would use a format of [h]:mm with the brakets around the h. That tells Excel to display the time value as an elapsed time and it would display 54:23. I don't know what the equivalent would be in Access.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to I add up hours/min (Access 97)

    Dan: I use this calculation to sum times:

    In the Control Source property:

    =Int(Sum([timefield]))*24 + DatePart("h",Sum([timefield])) & ":" & DatePart("n",Sum([timefield]))

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to I add up hours/min (Access 97)

    This last one did it. Worked great. Now need to make sure that I keep this and don't louse it.

    Thank you

Posting Permissions

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