Hi,
I have an excel file with all the employees in and out punch time in a very jumbled format. Have attached a sample for your easy reference.
There could be multiple punch times for one employee. I would like to have 1st In time and last out time captured for each employee in a different sheet. If out time is not available, the cell value against that particular employee will show blank. The values that need to be capture to another sheet is as below
S.No. Date of Punch Employee Name In Time Out Time Device/Source Detail Location Punch Mode Event Status Special Function
I have an excel file with all the employees in and out punch time in a very jumbled format. Have attached a sample for your easy reference.
In-Out Time.xls | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | |||
2 | User ID | Name | Punch Time | I/O Type | Device/Source Detail | Location | Punch Mode | Event Status | Special Function | |||
3 | ||||||||||||
4 | ||||||||||||
5 | 25/10/2019 - Date of Punch | |||||||||||
6 | AA | PRAMOD KUMAR MAJHEE | 00:03:36 | IN | ||||||||
7 | ||||||||||||
8 | AA | PRAMOD KUMAR MAJHEE | 00:05:39 | OUT | ||||||||
9 | ||||||||||||
10 | BB | PRABAKARAN.N | 00:06:18 | IN | ||||||||
11 | ||||||||||||
12 | BB | PRABAKARAN.N | 00:06:42 | IN | ||||||||
13 | ||||||||||||
14 | CC | PRAMOD KUMAR MAJHEE | 00:38:59 | IN | ||||||||
15 | ||||||||||||
16 | CC | PRAMOD KUMAR MAJHEE | 00:41:04 | OUT | ||||||||
17 | ||||||||||||
18 | CC | PRAMOD KUMAR MAJHEE | 00:56:40 | IN | ||||||||
19 | ||||||||||||
20 | CC | PRAMOD KUMAR MAJHEE | 01:03:18 | IN | ||||||||
21 | ||||||||||||
22 | CC | PRAMOD KUMAR MAJHEE | 01:03:23 | OUT | ||||||||
23 | ||||||||||||
24 | DD | SOUMYA RANJAN MOHANTY | 01:32:01 | IN | ||||||||
25 | ||||||||||||
26 | DD | SOUMYA RANJAN MOHANTY | 01:34:24 | OUT | ||||||||
27 | ||||||||||||
28 | DD | SOUMYA RANJAN MOHANTY | 03:07:00 | IN | ||||||||
29 | ||||||||||||
30 | DD | SOUMYA RANJAN MOHANTY | 03:09:49 | OUT | ||||||||
31 | ||||||||||||
32 | EE | PRAMOD KUMAR MAJHEE | 05:14:21 | IN | ||||||||
33 | ||||||||||||
34 | EE | PRAMOD KUMAR MAJHEE | 05:49:11 | OUT | ||||||||
35 | ||||||||||||
Sheet1 |
There could be multiple punch times for one employee. I would like to have 1st In time and last out time captured for each employee in a different sheet. If out time is not available, the cell value against that particular employee will show blank. The values that need to be capture to another sheet is as below
S.No. Date of Punch Employee Name In Time Out Time Device/Source Detail Location Punch Mode Event Status Special Function