Calculating hours missed from actual shift schedule using date/time

[access_learner]

I want to calculate the hours missed by worker from Scheduled_shift_start_at
and Scheduled_shift_end_at using date/time. The worker either started
his/her shift early or came in late. Also, the worker either finished early
if started early or finished later if started later than
Scheduled_shift_ends_at.

I have four columns of data as sample provided below. I need to find out the
total hours the worker worked outside of scheduled shift. The scheduled
shift goes from 9 am to 6 pm. The date changes if worker works night shift.


Worker_started_at Scheduled_shift_starts_at Worker_finished_at
Scheduled_shift_ends at
06/01/2009 06:30:00 06/01/2009 09:00:00 06/01/2009 15:00:00 06/01/2009 18:00:
00
06/01/2009 08:00:00 06/01/2009 09:00:00 06/01/2009 16:30:00 06/01/2009 18:00:
00
06/01/2009 23:30:00 06/01/2009 09:00:00 06/02/2009 08:00:00 06/01/2009 18:00:
00
06/01/2009 08:00:00 06/01/2009 09:00:00 06/01/2009 16:30:00 06/01/2009 18:00:
00
06/01/2009 14:30:00 06/01/2009 09:00:00 06/01/2009 23:00:00 06/01/2009 18:00:
00

 

[Allen Browne]

In query design view, type an expression like this into a fresh column in
the Field row:
MinutesWorked: DateDiff("n", [Worker_started_at], [Worker_finished_at])
That shows the number of minutes the person worked.

In the next column:
MinutesSched: DateDiff("n", [Scheduled_shift_starts_at],
[Scheduled_shift_ends at])

To calculate the difference between the two (in minutes):
DateDiff("n", [Scheduled_shift_starts_at], [Scheduled_shift_ends at]) -
DateDiff("n", [Worker_started_at], [Worker_finished_at])