Connecting time booking inbetween specific attendance date

[Raymie]

Hi

Am looking for help. I have two tables in power pivot. The first one show employee attendance (see example below). The second shows employee daily booking (see example below). What am looking to do is show all employee bookings within the employees attendance. I can do this for the employees that work day shift & back shift because all there booking are on the same day as there attendance. The problem I have is with night shift because the attendance and bookings are over two days. Is there anyway I can create a relationship or formula within power pivot that links the bookings between the employees attendance time.

Table 1
[TABLE="width: 330"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Operator ID[/TD]
[TD]Start Date/Time[/TD]
[TD]Finish Date/Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/03/2018 06:00[/TD]
[TD]01/03/2018 14:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/03/2018 06:00[/TD]
[TD]01/03/2018 14:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/03/2018 14:00[/TD]
[TD]01/03/2018 22:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/03/2018 14:00[/TD]
[TD]01/03/2018 22:00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/03/2018 22:00[/TD]
[TD]02/03/2018 06:00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]01/03/2018 22:00[/TD]
[TD]02/03/2018 06:00[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 436"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Operator ID[/TD]
[TD]Booking Start Date/Time[/TD]
[TD]Booking Finish Date/Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/03/2018 06:00[/TD]
[TD]01/03/2018 08:24[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/03/2018 08:24[/TD]
[TD]01/03/2018 10:48[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/03/2018 10:48[/TD]
[TD]01/03/2018 13:12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/03/2018 14:00[/TD]
[TD]01/03/2018 16:24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/03/2018 16:24[/TD]
[TD]01/03/2018 18:48[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/03/2018 18:48[/TD]
[TD]01/03/2018 21:12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/03/2018 22:00[/TD]
[TD]02/03/2018 00:24[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/03/2018 00:24[/TD]
[TD]02/03/2018 02:48[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/03/2018 02:48[/TD]
[TD]02/03/2018 05:12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/03/2018 06:00[/TD]
[TD]02/03/2018 08:24[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/03/2018 08:24[/TD]
[TD]02/03/2018 10:48[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/03/2018 10:48[/TD]
[TD]02/03/2018 13:12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]02/03/2018 14:00[/TD]
[TD]02/03/2018 16:24[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]02/03/2018 16:24[/TD]
[TD]02/03/2018 18:48[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]02/03/2018 18:48[/TD]
[TD]02/03/2018 21:12[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]02/03/2018 22:00[/TD]
[TD]03/03/2018 00:24[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]03/03/2018 00:24[/TD]
[TD]03/03/2018 02:48[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]03/03/2018 02:48[/TD]
[TD]03/03/2018 05:12[/TD]
[/TR]
</tbody>[/TABLE]

 

[akice]

What you are looking to accomplish is not clear. Can you provide a sample of desired output?

 

[Raymie]

Am looking to calculate daily employee utilization (number of hours booked/number of hours attended). I can do this for employees on day shift and back shift okay because all there bookings and attendance is on the one day. The problem I get is night shift where there bookings can be spread over two days. I would like to relate all the booking from night shift to there attendance start date.