Formatting of Bio-metric attendence csv file

[rehanahmadawan]

Hi,

I hope I was able to find the correct forum/title for the query I have. I am pretty much stuck with my task today and I really need help with regards to what I have.

I am a new HR Associate and a part of my task is to decode/interpret our bio-metric data. Currently we have over 100 employees with different shift and rest days being recorded onto our bio metric system. What I am trying to do is to be able to come up with a spreadsheet that could help me analyze the data first before I can even interpret it.

This is how our raw biometric data looks like (in excel format)

251001	Peach	5/18/2014	I	13:47
251001	Peach	5/18/2014	O	18:33
251001	Peach	5/18/2014	I	19:46
251001	Peach	5/18/2014	O	23:07
251001	Peach	5/20/2014	I	12:16
251001	Peach	5/20/2014	O	18:31
251001	Peach	5/20/2014	I	19:22
251001	Peach	5/20/2014	O	23:09
251001	Peach	5/21/2014	I	19:37
251001	Peach	5/21/2014	O	23:09
251001	Peach	5/22/2014	I	13:46

<tbody>
</tbody>

I=in time, O= out time, and first column is employee ID.

want to separate an individual time in and out in separate columns for a single day/date.
for example :
Employee ID Name Date Time in Time Out working Hours
251001 peach 5/18/2014 13:47 18:33 --

Thanks in advance.

 

[awoohaw]

You can use power query to "PIVOT" the last two columns. Or use a pivot table.
(Sorry this does't work as you have multiple days with IN/OUT times.

Here is a pivot table:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Name	Date	In or Out	Time			Time In and Out		In or Out
2	251001	Peach	2014-05-18	I	13:47			Name	Date	I	O
3	251001	Peach	2014-05-18	O	18:33			Peach	2014-05-18	09:33	17:40
4	251001	Peach	2014-05-18	I	19:46			Peach	2014-05-20	07:38	17:40
5	251001	Peach	2014-05-18	O	23:07			Peach	2014-05-21	19:37	23:09
6	251001	Peach	2014-05-20	I	12:16			Peach	2014-05-22	13:46
7	251001	Peach	2014-05-20	O	18:31
8	251001	Peach	2014-05-20	I	19:22
9	251001	Peach	2014-05-20	O	23:09
10	251001	Peach	2014-05-21	I	19:37
11	251001	Peach	2014-05-21	O	23:09
12	251001	Peach	2014-05-22	I	13:46
13
14
Sheet4