Calculating Time Data

[michwh1]

I have a file that collects the time data of the shop employees. It records time in (start of day), time out (lunch start), time in (lunch end) and time out (end of day).

Sample of the table:

In/Out Employee ID Date Time
0 316 11/2/2006 7:00:34 AM
0 1244 11/2/2006 7:05:00 AM
1 316 11/2/2006 12:00:43 PM
1 1244 11/2/2006 12:01:21 PM
0 316 11/2/2006 12:30:43 PM
0 1244 11/2/2006 12:34:25 PM
1 316 11/2/2006 3:30:46 PM
1 1244 11/2/2006 3:34:34 PM

0 = In, 1 = Out

How can I calculate the total hours worked per day(First In + First Out + (Second In + Second Out).

I appreciate any help.

 

[neileg]

Date/time is stored as a number where the integer part is the day and the decimal fraction represents the time. So if you subtract one date/time from another you get the difference. Normally Access will format this as a meaningless date and time, but once you know it is really a number you can do the maths.

So if you take the time in and make it negative, you can sum the times per emplyee and get the attended hours. The formula would look like this.
HoursAttended: IIf([inOut]=0,[DateTime]*-1,[DateTime])*24
In a Totals query you would group by EmployeeID and Date and get the number of hours for each day.

 

[kalebson]

Or convert your original date time to a "second start" using 0 as midnight.

 

[michwh1]

Thanks

Thanks. That helps

 

[michwh1]

Still an issue

I am still having a problem with this one. The key issue is that the times are all in the same field and I am unsure how to distinguish the first in/out times from the second.

 

[neileg]

The formula I suggested does cater for that.

 

[michwh1]

Thanks

You're right. Works fine. I appreciate the help.