Determining the first & last timestamp per day, per user

MrNumbers

New Member
Joined
Jan 18, 2010
Messages
9
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
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top