Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2004
    Location
    Sydney, New South Wales, Australia
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating hours & minutes between dates & times (Access2000 sp3)

    Another date/time question.

    I have a table that has a start date, start time, finish date and finish time fields. What I would like to do is create a query (using the query builder) that calculates the number of hours and minutes between the start date, start time and the finish date, finish time.

    Suggestions on how to go about this are much appreciated.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Calculating hours & minutes between dates & times (Access2000 sp3)

    Date and times are both stored as numbers, and can be combined in a single field. In this case whole number part represents the date, and the decimal parts represents the time.

    If you keep them in different fields, then adding the two fields, gives a values that represents the date and time. You can then use the DateDiff function to find the number of time intervals between those two times. So you might use:

    minsdiff: DateDiff("n",[startdate]+[starttime],[finishdate]+[finishtime])

    This will tell you the number of minutes.

    Divide that by 60 and only take the whole number part of the answer and you have the number of hours. The operator does that.

    The remainder is the number of minutes. The mod operator does that.

    Hoursdiff: [minsdiff] 60
    Minsleft: [minsdiff] mod 60
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating hours & minutes between dates & times (Access2000 sp3)

    ...and in addition to John's answer you can format it like this:

    Difference: [minsdiff]60 & " hours " & [minsdiff] Mod 60 & " minutes"

    to show it as 23 hours 33 minutes
    Jerry

  4. #4
    Star Lounger
    Join Date
    Sep 2004
    Location
    Sydney, New South Wales, Australia
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating hours & minutes between dates & times (Access2000 sp3)

    Thanks John and Jerry , your solutions work great.

Posting Permissions

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