reighzaigne
New Member
- Joined
- Oct 19, 2015
- Messages
- 28
Hi!
Our biometrics had a glitch and I have to manually check the data extracted from the machine. There are multiple logs in a day from different people. I would like to know the formula to get the First In and Last Out per person per day. I initially concatenated the employee number and dates to somewhat create a code. So now I just need to get the first and last logs.
Of if you have other ideas on how to solve it, please let me know.
Here's an example:
<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
And then I would like to make it into something like this:
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
You can check the file here:
https://drive.google.com/open?id=1H2R5QWzsJRNop3RzsulFIajK9bqwXKDq
Thank you!
Our biometrics had a glitch and I have to manually check the data extracted from the machine. There are multiple logs in a day from different people. I would like to know the formula to get the First In and Last Out per person per day. I initially concatenated the employee number and dates to somewhat create a code. So now I just need to get the first and last logs.
Of if you have other ideas on how to solve it, please let me know.
Here's an example:
Concatenate | EnNo | DateTime | Date | Time |
643467 | 6 | 02/01/19 8:47 | 02/01/19 | 8:47:07 AM |
643467 | 6 | 02/01/19 15:42 | 02/01/19 | 3:42:31 PM |
843468 | 8 | 03/01/19 4:44 | 03/01/19 | 4:44:47 AM |
843468 | 8 | 03/01/19 4:44 | 03/01/19 | 4:44:47 AM |
643468 | 6 | 03/01/19 8:59 | 03/01/19 | 8:59:15 AM |
343468 | 3 | 03/01/19 9:29 | 03/01/19 | 9:29:31 AM |
243468 | 2 | 03/01/19 10:42 | 03/01/19 | 10:42:05 AM |
243468 | 2 | 03/01/19 10:42 | 03/01/19 | 10:42:05 AM |
843468 | 8 | 03/01/19 13:45 | 03/01/19 | 1:45:11 PM |
643468 | 6 | 03/01/19 17:00 | 03/01/19 | 5:00:30 PM |
343468 | 3 | 03/01/19 17:09 | 03/01/19 | 5:09:43 PM |
343468 | 3 | 03/01/19 17:09 | 03/01/19 | 5:09:43 PM |
243468 | 2 | 03/01/19 19:53 | 03/01/19 | 7:53:31 PM |
243468 | 2 | 03/01/19 19:53 | 03/01/19 | 7:53:31 PM |
243468 | 2 | 03/01/19 21:08 | 03/01/19 | 9:08:53 PM |
<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
And then I would like to make it into something like this:
DATE | In | Out | Duration | |
243486 | 21/01/19 | 02:10:59 AM | ||
243487 | 22/01/19 | |||
243488 | 23/01/19 | |||
243489 | 24/01/19 | |||
243490 | 25/01/19 |
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
You can check the file here:
https://drive.google.com/open?id=1H2R5QWzsJRNop3RzsulFIajK9bqwXKDq
Thank you!