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

1. ## 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.

2. ## 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

3. ## 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

4. ## 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
•