Connecting time booking inbetween specific attendance date

Raymie

New Member
Joined
Jan 29, 2018
Messages
7
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]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
What you are looking to accomplish is not clear. Can you provide a sample of desired output?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,700
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top