How to calculate time differences between 2 time intervals in columns of my table, in

[SamarKhanimcq]

Ive set 2 coulumns of my table as "Time In" and "Time Out".
I want Access to calculate the time difference automatically and then i want to run a query that pulls out all records in which the time differnce is greater than 3 hours.
Can someone please take me through the steps i would have to take.
Thanks!

[MikeTheBike]

Hi

On the basis that for a given record Time In and Time Out are both for the same day and are DateTime fields, then a query something like this should be near the mark

SELECT YourTable.*, (YourTable.[Time Out]-YourTable.[Time In])*24 as DurationHours FROM YourTable WHERE (YourTable.[Time Out]-YourTable.[Time In])*24 > 3

??

MTB

[Samar Khan]

Thank you so much for your help.
I have decided to work in excel now, and have a few questions.
1. i need a function that allows all the values in coloumn 3 to be a value given by the respective Time Out minus Time In.
2. Another problem is that around half the Time Outs are a date of the following morning, can i make a function that adds 24 to all the negative values produced in the 3rd coloumn.
3. and then i need a filter that seperates all record in which the Length of Stay is greater than 3, how can i do that?

Thank you again for all your time.

Samar Khan

[MikeTheBike]

Hi

Again assuming C4 is the Time Out and D4 is Time In AND Time Out is the same day OR one day later ONLY (and are times) then this should do it
=(C4-D4+IF(C4<D4,1,0 ))*24

This assumes any Time Out's that are less than the Time In are the next day. However this is subject to errors unless you can guarantee this will ALLWAYS be the case!

Not sure how/what you require/intend for point 3.
ie. is this a global filter on the sheet or a filter on the calculated value?

MTB

[Samar khan]

Hi,

Sorry i dont think that function was as right as i initially thought.
Im getting a value, but it isnt correct exactly.
For eg. the value for a Time In 00:06 and Time Out 09:28 (in 24hr time) is giving me 9:37, as opposed to 9:22.

and i think there may be something missing in your function "=(C4-D4+IF(C4<D4,1,0 ))*24". i need to have a 24 added to all the C4 values that are less than D4, before it calculates C4-D4

thanks again for all your help MTB.

[MikeTheBike]

Hi

If you have the cell formatted as 'Time' ie hh:mm
then the formula should be

=(C4-D4+IF(C4<D4,1,0 ))

ie. do not convert it to hours, but leave it as a fraction of a day, the formatting displays it as hours.

That is 0.390278 days (or 9.366667 hour) is 9 hours 22 minutes.

MTB