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:
<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:
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
