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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,952
Members
413,954
Latest member
mrsandy

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