Select first in and Last out time for a day from multiple In and Out entries for each day

[vij]

Hi,
Require urgent help for the following:

I have multiple in and out data for number of employees coming to office for a number of days. The employees come to office in three shifts as under:

First Shift: 6:00 AM to 3:00 PM
Second Shift: 2:00 PM to 11:00 PM
Third Shift: 9:00 PM to 6:00 AM.
I want to select the first in and last out data for each employee for each day and then calculate the total number of hours spent in the office by each employee per day.
A sample data is as under:

FIRST_NAME EMPLOYEE LDAP_ID READER_DESC LOCAL_DATE LOCAL_TIME Akshay Aggarwal 41088 aksaggar 821-7-01 NOI:TURN 1 OUT FL7 20121108 02:04:59 Akshay Aggarwal 41088 aksaggar 821-8-00 NOI:TURN 2 IN FL7 20121108 02:20:28 Akshay Aggarwal 41088 aksaggar 821-7-01 NOI:TURN 1 OUT FL7 20121108 03:14:26 Akshay Aggarwal 41088 aksaggar 821-8-00 NOI:TURN 2 IN FL7 20121108 18:31:07 Akshay Aggarwal 41088 aksaggar 821-7-01 NOI:TURN 1 OUT FL7 20121108 21:47:35 Akshay Aggarwal 41088 aksaggar 821-8-00 NOI:TURN 2 IN FL7 20121108 22:19:24 Taufique Ahmad 37716 taahmad 821-8-00 NOI:TURN 2 IN FL7 20121108 14:26:49 Taufique Ahmad 37716 taahmad 821-7-01 NOI:TURN 1 OUT FL7 20121108 22:15:11 Taufique Ahmad 37716 taahmad 821-8-00 NOI:TURN 2 IN FL7 20121108 22:39:50 Taufique Ahmad 37716 taahmad 821-7-01 NOI:TURN 1 OUT FL7 20121108 23:25:27 Akshay Aggarwal 41088 aksaggar 821-8-01 NOI:TURN 2 OUT FL7 20121109 01:59:25 Akshay Aggarwal 41088 aksaggar 821-8-00 NOI:TURN 2 IN FL7 20121109 02:17:23 Akshay Aggarwal 41088 aksaggar 821-7-01 NOI:TURN 1 OUT FL7 20121109 03:19:10 Akshay Aggarwal 41088 aksaggar 821-7-00 NOI:TURN 1 IN FL7 20121109 18:09:38 Taufique Ahmad 37716 taahmad 821-8-00 NOI:TURN 2 IN FL7 20121109 14:17:49 Taufique Ahmad 37716 taahmad 821-7-01 NOI:TURN 1 OUT FL7 20121109 23:25:33 <tbody> </tbody>

<tbody>
</tbody>

The data is required in the following format

	Date	Total Hours	Akshay Aggarwal	Taufique Ahmad	l
In	08-Nov-12	hh:mm:ss
Out	08-Nov-12	hh:mm:ss <tbody> </tbody>
Total Hours		hh:mm:ss (Out - In) <tbody> </tbody>
In	09-Nov-12
Out	09-Nov-12
Total Hours
In	10-Nov-12
Out	10-Nov-12
Total Hours

<tbody>
</tbody>

Thanks in advance,
Vij

 

[Andrew Poulsom]

Are those LOCAL_DATEs serial dates formatted as yyyymmdd or just numbers?

 

[vij]

Yes these are dates in yyyymmdd

 

[Andrew Poulsom]

Why is Akshay Aggarwal leaving at about 3AM and starting at about 6PM? That doesn't fit the shift pattern.

 

[vij]

As I mentioned in my post, there are three shifts in which the employees come for duty. On this particular day Akshay would have reported for the shift accordingly or would have got late/ reported early for duty.

 

[vij]

Also the In/ Out status is taken from READER_DESC "821-7-01 NOI:TURN 1 OUT FL7", "821-8-00 NOI:TURN 2 IN FL7"

 

[Andrew Poulsom]

If your first table is in the range A1:F17 on Sheet1 and your second table is in the range A1:E10 on Sheet2, for the first in in D2 on Sheet2:

=MIN(IF(Sheet1!$A$2:$A$17=D$1,IF(ISNUMBER(SEARCH(" "&$A2&" ",Sheet1!$D$2:$D$17)),IF(Sheet1!$E$2:$E$17=$B2,(Sheet1!$F$2:$F$17+(Sheet1!$F$2:$F$17<"06:00"+0))))))

confirmed with Ctrl+Shift+Enter. For the last out in D3:

=MAX(IF(Sheet1!$A$2:$A$17=D$1,IF(ISNUMBER(SEARCH(" "&$A3&" ",Sheet1!$D$2:$D$17)),IF((Sheet1!$E$2:$E$17-(Sheet1!$F$2:$F$17<"06:00"+0))=$B3,(Sheet1!$F$2:$F$17+(Sheet1!$F$2:$F$17<"06:00"+0))))))

confirmed with Ctrl+Shift+Enter.

Copy the formulas down and across.

 

[vij]

I am getting the value for both "IN" and "Out" as 00:00:00

 

[Andrew Poulsom]

Did you press Ctrl+Shift+Enter not just Enter? if entered correctly Excel will surround the formula with curly braces {}. Results using your data:

Excel 2003
	A	B	C	D	E
1		Date	Total Hours	Akshay Aggarwal	Taufique Ahmad
2	In	08-Nov-12	hh:mm:ss	18:31:07	14:26:49
3	Out	08-Nov-12	hh:mm:ss	03:19:10	23:25:27
4	Total Hours		hh:mm:ss (Out - In)
5	In	09-Nov-12		18:09:38	14:17:49
6	Out	09-Nov-12		00:00:00	23:25:33
Sheet2

 

[vij]

Sorry, I am using excel 2010 on win 7, still not getting the result.