Getting First In and Last out from a temp data

Hi everyone,

I am in need of help developing a formula to get the first in and last out from a biometrics logs that I have, here below is a sample of the raw data that will provide me

[CardNo] Name [Company] [Date] [Time] [Action] [ActionText]
605 User1 Department 1 26/04/2016 1:21:20 PM 1 ENTER
605 User1 Department 1 26/04/2016 1:22:36 PM 0 LEAVE
28 User3 Department 1 26/04/2016 1:31:55 PM 1 ENTER
34 User5 Department 1 26/04/2016 1:31:57 PM 1 ENTER
605 User1 Department 1 26/04/2016 1:33:19 PM 1 ENTER
601 User6 Department 1 26/04/2016 1:37:21 PM 1 ENTER
605 User1 Department 1 26/04/2016 1:38:39 PM 0 LEAVE
605 User1 Department 1 27/04/2016 1:40:10 PM 1 ENTER
28 User3 Department 1 27/04/2016 1:43:17 PM 0 LEAVE
1007 User8 Department 1 27/04/2016 1:45:34 PM 1 ENTER
32 User4 Department 1 27/04/2016 1:46:32 PM 0 LEAVE
28 User3 Department 1 27/04/2016 1:47:06 PM 1 ENTER
1007 User8 Department 1 27/04/2016 1:49:50 PM 0 LEAVE
1007 User8 Department 1 28/04/2016 1:49:58 PM 1 ENTER
1007 User8 Department 1 28/04/2016 1:50:11 PM 1 ENTER
1007 User8 Department 1 28/04/2016 1:55:10 PM 0 LEAVE
605 User1 Department 1 28/04/2016 2:02:44 PM 0 LEAVE
605 User1 Department 1 28/04/2016 2:06:43 PM 0 LEAVE
6 User2 Department 1 28/04/2016 2:23:00 PM 0 LEAVE
6 User2 Department 1 28/04/2016 2:24:28 PM 1 ENTER
605 User1 Department 1 29/04/2016 2:25:21 PM 0 LEAVE
605 User1 Department 1 29/04/2016 2:27:42 PM 1 ENTER
999 User7 Department 1 29/04/2016 2:37:40 PM 0 LEAVE
34 User5 Department 1 29/04/2016 2:39:28 PM 0 LEAVE
601 User6 Department 1 29/04/2016 2:41:07 PM 0 LEAVE
28 User3 Department 1 29/04/2016 2:42:19 PM 0 LEAVE
34 User5 Department 1 29/04/2016 2:42:48 PM 1 ENTER


I was hoping to get a good report out of this data

From how it looks above to

Date Date
Card Number User Department Time In Time Out Time In Time Out


Thanks in advance to those who 'll reply.

Hi,

Thanks for the response, but unfortunately, the data seems to get a 0 value after changing the settings to MIN/MAX

Thanks for this JeteMc. I will take note of the cross posting. I was actually in need of help for this.

Though, is still possible to get the first in and last out for a specific day and specific user.

In the sheet I've provided, As I see, what if I have multiple Enter for just 1 day, and vice versa for the Leave, what would be my identifier for that?

I can see your formula to come up with Less than or More than identifier, if I am correct.

As well, if the user comes in at night, probably around 20:00 and left at 06:00, it will jump the next day. How will my formula for this be?