Need to extract In & Out time of employees

[leena24]

Hi

I have an in & Out time of employees and I need to find out , how much time they have spent out of a particular floor on a particular day.

Unique field are date and the card number which is used at entry and exit point of the floor. There is a time field which provides time spent in or outside the floor.

'reader in' means employee entered and 'reader out' means employee went out. The time difference on a particular date's 'reader in' & 'reader out' will
provide the actual time spent out of a particular floor. Which in my case is the 9th floor. Which means how much time an employee spent in a day out of 09th floor.

The data is for entire month and for multiple employees (which could be identifyied basis unique "card number".

The spread sheet looks like this, Date is in column A , Time in b , Reader in C, Card Number in D, name in E :
Date Time Reader Card Number Name
4/1/2015 10:01:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 41363 XYZ
4/1/2015 11:53:00 AM 9 FLOOR FIRE EXIT C13 - Reader OUT 41363 XYZ
4/1/2015 11:54:00 AM 11 FLOOR FIRE EXIT - Reader IN 41363 XYZ
4/1/2015 12:05:00 PM 11 FLOOR FIRE EXIT - Reader OUT 41363 XYZ
4/1/2015 12:06:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 41363 XYZ
4/1/2015 2:16:00 PM 9 FLOOR FIRE EXIT C13 - Reader OUT 41363 XYZ
4/1/2015 2:17:00 PM 11 FLOOR FIRE EXIT - Reader IN 41363 XYZ
4/1/2015 2:39:00 PM 11 FLOOR FIRE EXIT - Reader OUT 41363 XYZ
4/1/2015 2:40:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 41363 XYZ
4/1/2015 2:42:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 41363 XYZ
4/1/2015 3:07:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 41363 XYZ
4/1/2015 7:13:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 41363 XYZ
4/1/2015 8:48:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 10:56:00 AM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 11:06:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 11:37:00 AM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 11:49:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 3:30:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 6481 ABC
4/1/2015 6:00:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6481 ABC
4/1/2015 10:06:00 AM 9 FLOOR RECEPTION C 14 - Reader IN 6894 EFG
4/1/2015 1:07:00 PM 9 FLOOR FIRE EXIT C13 - Reader OUT 6894 EFG
4/1/2015 1:08:00 PM 11 FLOOR FIRE EXIT - Reader IN 6894 EFG
4/1/2015 1:22:00 PM 11 FLOOR FIRE EXIT - Reader OUT 6894 EFG
4/1/2015 1:23:00 PM 9 FLOOR FIRE EXIT C13 - Reader IN 6894 EFG
4/1/2015 2:15:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6894 EFG
4/1/2015 2:49:00 PM 9 FLOOR RECEPTION C 14 - Reader IN 6894 EFG
4/1/2015 7:12:00 PM 9 FLOOR RECEPTION C 14 - Reader OUT 6894 EFG




As an end out put following is required, for each employee, date wise :

Date | Card number | name | total time out of 9th floor | time spent on other floors |


Request if anyone in the forum lets me know if this could be done in excel and help me in getting this. Pls note, I use excel 2002 version.


Regards.
Leena

 

[yky]

Please post a photo of the spreadsheet. As it is now, it is difficult to tell what exactly is in the columns.

 

[dmt32]

Hi,
Have you tried using autofilter? You then need only add a formula in a vacant cell to calculate the time difference

Dave

 

[leena24]

http://i61.tinpic.com/1i0lna.jpg

I have uploaded an image. Hope it helps.


I cannot use filters as the excel sheet has many employees. Using filters will take a lot of time

 

[yky]

Something wrong with the url. See a blank page.

 

[Andrew Fergus]

I cannot use filters as the excel sheet has many employees. Using filters will take a lot of time

How many rows are there? And in the first row of data is 41363 the card number?

 

[leena24]

In the first row : date is in cell a1, time is in cell b1 it ends at am/ pm, reader i n cell c1 it ends at IN/OUT, NUMERIC number is the card number in d1 and name in cell e1 which contains only alpha ( xyz, abc etc) . It keeps going on below for the next record. Hope it helps. Will try to upload the picture again.
Thanks

 

[Andrew Fergus]

Hi

To make this easier, I recommend you add some new columns to your data with formulas like this:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Time	Reading	Card#	Employee	Emp.Date	Unique	Floor	In/Out	Key	Last In	Elapsed
2	4/01/15	10:01:00 a.m.	9 FLOOR RECEPTION C 14 - Reader IN	41363	XYZ	42008XYZ	1	9	IN	42008XYZ9IN
3	4/01/15	11:53:00 a.m.	9 FLOOR FIRE EXIT C13 - Reader OUT	41363	XYZ	42008XYZ	1	9	OUT	42008XYZ9OUT	10:01:00	01:52:00
4	4/01/15	11:54:00 a.m.	11 FLOOR FIRE EXIT - Reader IN	41363	XYZ	42008XYZ	1	11	IN	42008XYZ11IN
5	4/01/15	12:05:00 p.m.	11 FLOOR FIRE EXIT - Reader OUT	41363	XYZ	42008XYZ	1	11	OUT	42008XYZ11OUT	11:54:00	00:11:00

<tbody>
</tbody>

Sheet2


The formulas are (copy all formulas down for your data):
F2 : =A2&E2
G2 : =MAX(G$1:G1)+(COUNTIF(F$1:F2,F2)=1)
H2 : =1*LEFT(C2,SEARCH(" ",C2))
I2 : =IF(ISERROR(SEARCH("IN",C2)),"OUT","IN")
J2 : =F2&H2&I2
K2 : =IF(I2="OUT",LOOKUP(2, 1/(J1:J$2=CONCATENATE(A2&E2&H2&"IN")), B1:B$2),"")
L2 : =IF(LEN(K2)>0,B2-K2,"")

Then build yourself a summary like this:

	O	P	Q	R	S	T	U
1	Summary:
2
3	Unique	Row	Date	Card	Name	On 9th	On Others
4	1	2	4 Jan 15	41363	XYZ	08:10:00	00:33:00
5	2	14	4 Jan 15	6481	ABC	05:09:00	00:00:00
6	3	21	4 Jan 15	6894	EFG	08:16:00	00:14:00
7		0				00:00:00	00:00:00
8		0				00:00:00	00:00:00

<tbody>
</tbody>

Sheet2

Create the headings and enter the value 1 into cell O4. I created this summary table to the right of the data in the same sheet.

The formulas are: (again copy them all down)
P4: =IF(LEN(O4)>0,MATCH(O4,G:G,0),0)
Q4: =IF($P4>0,INDEX($A:$E,$P4,1),"")
R4: =IF($P4>0,INDEX($A:$E,$P4,4),"")
S4: =IF($P4>0,INDEX($A:$E,$P4,5),"")
T4: =SUMIF(J:J,Q4&S4&"9OUT",L:L)
U4: =SUMIF(F:F,Q4&S4,L:L)-T4
O5: =IF(MAX(G:G)>O4,O4+1,"")

The summary will be pretty much self-maintaining, if you need extra rows just copy the last row down as many as you need. It would probably be better to move the summary to another sheet. Then you can apply some filters to look at particular employees. Please note the data and summary is sequence sensitive so that if you re-sort the data, you may end up with errors. The time columns (T & U) will need to be formatted as hh:mm:ss or some such variation.

I trust this helps.
Andrew

 

[leena24]

Many thanks Andrew. I will try this and will get back. What i have is a montly data for employees. Just hoping that it summarises daywise for each employee which i can later put a pivot to for final monthly summary. ?

 

[Rick Rothstein]

Please post a photo of the spreadsheet. As it is now, it is difficult to tell what exactly is in the columns.

I know the OP already responded to your request, but here is something to try first in the future... click "Reply With Quotes" for the message with the layout you can't read... if you are in Source Mode (where the first icon in the Reply Box's window, with the small capital A/slash/large underlined capital A, is highlighted), the default I believe, then the actual layout the OP posted will display... in this case, the format is readable and can be copied.