Calculate the difference between two times in Access

I am creating a report and I need a field that calculates the difference between the start and stop times. I.E., I want the field to subtract the stop time from the start time and return the decimal of an hour. See below

[stop] 10:00 - [start] 09:30 = 0.50

Is this possible. I did it once in Excel, but I needed 2 calculations (one to get the difference and one to round it using ceiling), but I am hoping there is a way to do it in a single calculation.

I tried simply subtracting the time and somehow got 0.21 as the answer for the above.

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Replies (3)

QA Guy CommElec

You need TWO fields in your table that both are DateTime datatype.

Then in a query use -- (DateDiff("n",[Start],[Stop])\6)/10

DateDiff give the difference in minutes, \6 divides by 6 giving an integer, which is then Divided by 10.

Build a little, test a little

9 people found this reply helpful

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Robert Drummond

Perfect!!! I was trying to do this, but for some reason I couldn't get it right. I think it was the \6 and/10 that was giving me trouble. I appreciate the help.

1 person found this reply helpful

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Scottgem

Does the elapsed time ever span more than 24 hours? If it doesn't then you should be fine. If it does, however, then you need to include the date as well as the time.

You need to understand that Access stores time as a double precision number where the integer portion is the number of days since 12/30/1899 and the decimal portion a fraction of a day. So that .25 would be 6 AM (or 6 hours).

If you use this:

=(Stoptime - Starttime)*24 it should also work.

To use your numbers 10:00-09:30 would = 0.0208333. Multiple that by 24 and you get .49999992

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Calculate the difference between two times in Access

Replies (3) 

Question Info

Replies (3)