Attendance: In & Out per employee per day

**rnk4f** · 02-19-2020, 09:23 AM

Hi - I need help in finding a solution with my attendance record. Sorry I couldn't find relevant thread to find solution.
Only 2 columns, first column has date and time together - need to separate it to two columns. Second column has personnel ID.
Problem is the date and time has multiple records for one single day, meaning multiple in and multiple out. I need first in and last out per date per personnel ID.

My sheet looks like this.

Time Personnel ID
2020-02-05 21:15:35 202
2020-02-05 20:48:01 148

**AliGW** · 02-19-2020, 09:25 AM

Please attach a sample workbook with 10-15 rows of sample data (two lines, given your comments, are not enough.

**rnk4f** · 02-19-2020, 09:33 AM

Please find the sample workbook attached

**JeteMc** · 02-23-2020, 09:41 PM

As there are no designations for In/Out,this assumes that the first time listed for an ID on a given day is the first time signing in and the last time listed is for the last time signing out.
Column C displays the date using: =DATEVALUE(LEFT(A2,10))
Column D displays the time using: =TIMEVALUE(RIGHT(A2,8))
A pivot table displays the first in and last out which are the min of time for a day and max of time respectively.
Let us know if you have any questions.

**rnk4f** · 02-24-2020, 01:16 AM

Thank you so much

**JeteMc** · 02-24-2020, 10:29 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.