Excel Formula for Looking up Time In and Out

[reighzaigne]

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:
[TABLE="class: grid, width: 348"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Concatenate [/TD]
[TD]EnNo[/TD]
[TD]DateTime[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]643467[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]02/01/19 8:47[/TD]
[TD="align: right"]02/01/19[/TD]
[TD="align: right"]8:47:07 AM[/TD]
[/TR]
[TR]
[TD]643467[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]02/01/19 15:42[/TD]
[TD="align: right"]02/01/19[/TD]
[TD="align: right"]3:42:31 PM[/TD]
[/TR]
[TR]
[TD]843468[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]03/01/19 4:44[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]4:44:47 AM[/TD]
[/TR]
[TR]
[TD]843468[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]03/01/19 4:44[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]4:44:47 AM[/TD]
[/TR]
[TR]
[TD]643468[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]03/01/19 8:59[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]8:59:15 AM[/TD]
[/TR]
[TR]
[TD]343468[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]03/01/19 9:29[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]9:29:31 AM[/TD]
[/TR]
[TR]
[TD]243468[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]03/01/19 10:42[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]10:42:05 AM[/TD]
[/TR]
[TR]
[TD]243468[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]03/01/19 10:42[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]10:42:05 AM[/TD]
[/TR]
[TR]
[TD]843468[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]03/01/19 13:45[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]1:45:11 PM[/TD]
[/TR]
[TR]
[TD]643468[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]03/01/19 17:00[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]5:00:30 PM[/TD]
[/TR]
[TR]
[TD]343468[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]03/01/19 17:09[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]5:09:43 PM[/TD]
[/TR]
[TR]
[TD]343468[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]03/01/19 17:09[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]5:09:43 PM[/TD]
[/TR]
[TR]
[TD]243468[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]03/01/19 19:53[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]7:53:31 PM[/TD]
[/TR]
[TR]
[TD]243468[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]03/01/19 19:53[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]7:53:31 PM[/TD]
[/TR]
[TR]
[TD]243468[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]03/01/19 21:08[/TD]
[TD="align: right"]03/01/19[/TD]
[TD="align: right"]9:08:53 PM[/TD]
[/TR]
</tbody>[/TABLE]


And then I would like to make it into something like this:
[TABLE="class: grid, width: 330"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]DATE[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]243486[/TD]
[TD="align: right"]21/01/19[/TD]
[TD="align: right"]02:10:59 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]243487[/TD]
[TD="align: right"]22/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]243488[/TD]
[TD="align: right"]23/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]243489[/TD]
[TD="align: right"]24/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]243490[/TD]
[TD="align: right"]25/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



You can check the file here:
https://drive.google.com/open?id=1H2R5QWzsJRNop3RzsulFIajK9bqwXKDq


Thank you!

 

[AlanY]

try

Book1
	A	B	C	D	E
1		Cha	12:00:00 AM
2
3		DATE	In	Out	Duration
4	243486	21/01/2019	02:10:59 AM	11:43:34 PM	21:32:35
5	243487	22/01/2019	03:31:25 AM	07:38:49 PM	16:07:24
6	243488	23/01/2019	12:35:59 PM	09:41:47 PM	09:05:48
7	243489	24/01/2019	11:46:49 AM	09:04:43 PM	09:17:54
8	243490	25/01/2019	02:52:30 AM	08:51:55 PM	17:59:25
9	243491	26/01/2019	04:22:08 AM	11:59:45 PM	19:37:37
10	243492	27/01/2019	04:31:28 PM	10:46:00 PM	06:14:32
2 - Cha

Cell Formulas
Range	Formula
C4	=MINIFS(Sheet1!F:F,Sheet1!B:B,A4,Sheet1!E:E,B4)
D4	=MAXIFS(Sheet1!F:F,Sheet1!B:B,A4,Sheet1!E:E,B4)
E4	=D4-C4

 

[reighzaigne]

Thank you! But I get a #NAME ? error.

 

[AlanY]

the minif() & maxif() are not available for the older version of excel, try this

Book1
	A	B	C	D	E
1		Cha	12:00:00 AM
2
3		DATE	In	Out	Duration
4	243486	21/01/2019	02:10:59 AM	11:43:34 PM	21:32:35
5	243487	22/01/2019	03:31:25 AM	07:38:49 PM	16:07:24
6	243488	23/01/2019	12:35:59 PM	09:41:47 PM	09:05:48
7	243489	24/01/2019	11:46:49 AM	09:04:43 PM	09:17:54
8	243490	25/01/2019	02:52:30 AM	08:51:55 PM	17:59:25
9	243491	26/01/2019	04:22:08 AM	11:59:45 PM	19:37:37
10	243492	27/01/2019	04:31:28 PM	10:46:00 PM	06:14:32
11	243493	28/01/2019	12:43:45 AM	11:37:03 PM	22:53:18
2 - Cha

Cell Formulas
Range	Formula
E4	=D4-C4
C4	{=MIN(IF(Sheet1!$B1:$B10000='2 - Cha'!A4,IF(Sheet1!$E1:$E10000='2 - Cha'!B4,Sheet1!$F1:$F10000)))}
D4	{=MAX(IF(Sheet1!$B1:$B10000='2 - Cha'!A4,IF(Sheet1!$E1:$E10000='2 - Cha'!B4,Sheet1!$F1:$F10000)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[reighzaigne]

That one works perfectly well. Thank you so much! :D

 

[AlanY]

you're welcome