Determining the first & last timestamp per day, per user

MrNumbers

New Member
Hi
I have data that displays employees entering a security location (timestamp) and after the security process, exits the security location (timestamp) and enters the work area. Employees will then enter & exit the security area again after their shift ends. Employees can also move in and out of the security area as they leave to go on errands or lunch breaks.
What I want to achieve is determine how much time is spent inside the work area using the security in and out times as arrival and departure times to work out total time spent inside.
Sample data:
 Location Employee name Arrival Date Arrival Time Departure Date Departure Time Duration Debriefing room Bob Spoon 2019/02/16 13:57:09 2019/02/16 14:01:20 00:04:11 Debriefing room Bob Spoon 2019/02/17 01:24:56 2019/02/23 02:23:23 00:58:27 Debriefing room Bob Spoon 2019/02/23 18:11:43 2019/02/23 18:11:47 00:00:04 Debriefing room Bob Spoon 2019/02/23 18:11:47 2019/02/23 18:13:56 00:02:09 Debriefing room Bob Spoon 2019/02/23 18:14:50 2019/02/23 18:17:31 00:02:41 Debriefing room Bob Spoon 2019/02/23 18:17:34 2019/02/23 19:08:49 00:51:15 Debriefing room Bob Spoon 2019/02/27 21:38:46 2019/02/27 21:39:30 00:00:44 Debriefing room Bob Spoon 2019/02/28 16:58:18 2019/02/28 17:27:18 00:29:00 Debriefing room Dave Knorris 2019/02/18 09:50:31 2019/02/18 09:55:48 00:05:17 Debriefing room Dave Knorris 2019/02/18 10:59:01 2019/02/18 11:00:05 00:01:04 Debriefing room Dave Knorris 2019/02/19 08:35:24 2019/02/19 08:37:30 00:02:06 Debriefing room Sam Santiago 2019/02/19 09:49:39 2019/02/19 09:50:57 00:01:18 Debriefing room Sam Santiago 2019/02/19 10:23:02 2019/02/19 10:24:39 00:01:37 Debriefing room Sam Santiago 2019/02/19 12:32:41 2019/02/19 12:49:03 00:16:22

<tbody>
</tbody>

I want to achieve:
Look at the first arrival time on a specific date as “time in” and look at last “time in” on the same day. If no 2nd “time in” can be found, it means the employee worked a night shift crossing the midnight hour, resulting in you having to look for the first “time in” data on the following day. Keep in mind that the employee can then go home and come back again later that day for another shift, meaning you must account the 2nd “time in” on that date as the 1st “time in” for the next shift
Looking at Bob Spoon’s movements only:

• Start date C2 and time D2, leaving at C3 = total time spent in work area is 11:24:56

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Replies
20
Views
315
Replies
5
Views
76
Replies
10
Views
171
Replies
1
Views
114
Replies
12
Views
301

1,128,063
Messages
5,628,379
Members
416,314
Latest member
Dan99321

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.

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

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