Capture In and Out Time from a scrambled excel output

[Keerthiv]

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.

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

 

[etaf]

Is the USER ID unique to each employee , your sample shows
shows the same name for different IDs
Assuming the ID is unique

Setup a table with all the Employee IDs
thne you can use a MAXIFS() and a MINIFS()


Where column I has the list of IDs, this could be in a different sheet
=MAXIFS(D:D,E:E,"out",B:B,I2)
=MINIFS(D:D,E:E,"in",B:B,I2)

0 will return if not found, but an IF maybe used to change that

 

[Keerthiv]

Is the USER ID unique to each employee , your sample shows
shows the same name for different IDs
Assuming the ID is unique

Setup a table with all the Employee IDs
thne you can use a MAXIFS() and a MINIFS()


Where column I has the list of IDs, this could be in a different sheet
=MAXIFS(D:D,E:E,"out",B:B,I2)
=MINIFS(D:D,E:E,"in",B:B,I2)

0 will return if not found, but an IF maybe used to change that

Thank you.... this can be used if I only need the in and out time. However as mentioned in my post, I would need 1 row per employee with name and In/Out time in a separate sheet (headers below)

S.No. Date of Punch Employee Name In Time Out Time Device/Source Detail Location Punch Mode Event Status

 

[etaf]

also posted here

Capture In and Out Time from a scrambled excel output

Hi, I have a excel output from in-house software with the employees In and Out time in a very awkward way. Need this data to be shifted/copied to another sheet with employee name or id as cell reference. Below is the format that i am expecting as output. S. No. Date of Punch...

www.excelforum.com

As Asked
Is the USER ID unique to each employee , your sample shows
shows the same name for different IDs

 

[Keerthiv]

Oh Sorry...
Yes User Id is unique

 

[etaf]

answered in your other forum, with a pivot table example , but i don't think that fully solves your issue fully, can you change the layout slightly with the date in a different column or is this a fixed format

 

[Fluff]

@Keerthiv
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Keerthiv]

Dear All,

Please be informed that this query is also posted in another forum and here is the link

Capture In and Out Time from a scrambled excel output

Hi, I have a excel output from in-house software with the employees In and Out time in a very awkward way. Need this data to be shifted/copied to another sheet with employee name or id as cell reference. Below is the format that i am expecting as output. S. No. Date of Punch...

www.excelforum.com

 

[Keerthiv]

@ Fluff.. I was going through Rule # 13 of Forum Rules. Would like to notify that the link "Excelguru Help Site - A message to forum cross posters. " given is not working. It would be good if you can correct it.

 

[Fluff]

Thanks for that, but the link is working for me. Admittedly it's a bit slow, so there may be problems with the XlGuru website.