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

V

vij

Board Regular
Joined
Feb 13, 2011
Messages
215
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:
[TABLE="width: 596"]
<tbody>[TR]
[TD]FIRST_NAME		EMPLOYEELDAP_IDREADER_DESCLOCAL_DATELOCAL_TIME
Akshay Aggarwal41088aksaggar821-7-01 NOI:TURN 1 OUT FL72012110802:04:59
Akshay Aggarwal41088aksaggar821-8-00 NOI:TURN 2 IN FL72012110802:20:28
Akshay Aggarwal41088aksaggar821-7-01 NOI:TURN 1 OUT FL72012110803:14:26
Akshay Aggarwal41088aksaggar821-8-00 NOI:TURN 2 IN FL72012110818:31:07
Akshay Aggarwal41088aksaggar821-7-01 NOI:TURN 1 OUT FL72012110821:47:35
Akshay Aggarwal41088aksaggar821-8-00 NOI:TURN 2 IN FL72012110822:19:24
Taufique Ahmad37716taahmad821-8-00 NOI:TURN 2 IN FL72012110814:26:49
Taufique Ahmad37716taahmad821-7-01 NOI:TURN 1 OUT FL72012110822:15:11
Taufique Ahmad37716taahmad821-8-00 NOI:TURN 2 IN FL72012110822:39:50
Taufique Ahmad37716taahmad821-7-01 NOI:TURN 1 OUT FL72012110823:25:27
Akshay Aggarwal41088aksaggar821-8-01 NOI:TURN 2 OUT FL72012110901:59:25
Akshay Aggarwal41088aksaggar821-8-00 NOI:TURN 2 IN FL72012110902:17:23
Akshay Aggarwal41088aksaggar821-7-01 NOI:TURN 1 OUT FL72012110903:19:10
Akshay Aggarwal41088aksaggar821-7-00 NOI:TURN 1 IN FL72012110918:09:38
Taufique Ahmad37716taahmad821-8-00 NOI:TURN 2 IN FL72012110914:17:49
Taufique Ahmad37716taahmad821-7-01 NOI:TURN 1 OUT FL72012110923:25:33

<tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]

The data is required in the following format
[TABLE="width: 411"]
<tbody>[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Total Hours[/TD]
[TD]Akshay Aggarwal[/TD]
[TD]Taufique Ahmad[/TD]
[TD]l[/TD]
[/TR]
[TR]
[TD]In[/TD]
[TD]08-Nov-12[/TD]
[TD]hh:mm:ss[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]08-Nov-12[/TD]
[TD][TABLE="width: 411"]
<tbody>[TR]
[TD]hh:mm:ss[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[TD][TABLE="width: 411"]
<tbody>[TR]
[TD]hh:mm:ss (Out - In)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In[/TD]
[TD]09-Nov-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]09-Nov-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In[/TD]
[TD]10-Nov-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]10-Nov-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance,
Vij
 
Sort by date Sort by votes

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I have just copied and pasted the formula which you have mentioned above and then control+shift+enter
 
Upvote 0
Is you data in the same ranges as I described? You may need to adjust the references. Is the formula surrounded by curly braces {}.
 
Upvote 0
[TABLE="width: 1658"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Details[/TD]
[TD]Date[/TD]
[TD]Total Hours[/TD]
[TD]Akshay Aggarwal[/TD]
[TD]Taufique Ahmad[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]41221[/TD]
[TD]hh:mm:ss[/TD]
[TD]=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))))))[/TD]
[TD]=MIN(IF(Sheet1!$A$2:$A$17=E$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))))))[/TD]
[/TR]
[TR]
[TD]OUT[/TD]
[TD]41221[/TD]
[TD]hh:mm:ss[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[TD]hh:mm:ss (Out - In)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In[/TD]
[TD]41222[/TD]
[TD][/TD]
[TD]=MIN(IF(Sheet1!$A$2:$A$17=D$1,IF(ISNUMBER(SEARCH(" "&$A5&" ",Sheet1!$D$2:$D$17)),IF(Sheet1!$E$2:$E$17=$B5,(Sheet1!$F$2:$F$17+(Sheet1!$F$2:$F$17<"06:00"+0))))))[/TD]
[TD]=MIN(IF(Sheet1!$A$2:$A$17=E$1,IF(ISNUMBER(SEARCH(" "&$A5&" ",Sheet1!$D$2:$D$17)),IF(Sheet1!$E$2:$E$17=$B5,(Sheet1!$F$2:$F$17+(Sheet1!$F$2:$F$17<"06:00"+0))))))[/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]41222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In[/TD]
[TD]41223[/TD]
[TD][/TD]
[TD]=MIN(IF(Sheet1!$A$2:$A$17=D$1,IF(ISNUMBER(SEARCH(" "&$A8&" ",Sheet1!$D$2:$D$17)),IF(Sheet1!$E$2:$E$17=$B8,(Sheet1!$F$2:$F$17+(Sheet1!$F$2:$F$17<"06:00"+0))))))[/TD]
[TD]=MIN(IF(Sheet1!$A$2:$A$17=E$1,IF(ISNUMBER(SEARCH(" "&$A8&" ",Sheet1!$D$2:$D$17)),IF(Sheet1!$E$2:$E$17=$B8,(Sheet1!$F$2:$F$17+(Sheet1!$F$2:$F$17<"06:00"+0))))))[/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]41223[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Results when formatted as hh:mm:ss:


Excel 2003
ABCDE
1DetailsDateTotal HoursAkshay AggarwalTaufique Ahmad
2IN41221hh:mm:ss18:31:0714:26:49
3OUT41221hh:mm:ss
4Total Hourshh:mm:ss (Out - In)
5In4122218:09:3814:17:49
6Out41222
7Total Hours
8In4122300:00:0000:00:00
9Out41223
10Total Hours
11
Sheet8
 
Upvote 0
I seem to be goofing up some where:mad: The data is being picked up from Sheet1 as under fro A1 to F17

[TABLE="width: 654"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]EMPLOYEE[/TD]
[TD]LDAP_ID[/TD]
[TD]READER_DESC[/TD]
[TD]LOCAL_DATE[/TD]
[TD]LOCAL_TIME[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akshay Aggarwal[/TD]
[TD="align: right"]41088[/TD]
[TD]aksaggar[/TD]
[TD]821-7-01 NOI:TURN 1 OUT FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]02:04:59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akshay Aggarwal[/TD]
[TD="align: right"]41088[/TD]
[TD]aksaggar[/TD]
[TD]821-8-00 NOI:TURN 2 IN FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]02:20:28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akshay Aggarwal[/TD]
[TD="align: right"]41088[/TD]
[TD]aksaggar[/TD]
[TD]821-7-01 NOI:TURN 1 OUT FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]03:14:26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akshay Aggarwal[/TD]
[TD="align: right"]41088[/TD]
[TD]aksaggar[/TD]
[TD]821-8-00 NOI:TURN 2 IN FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]18:31:07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akshay Aggarwal[/TD]
[TD="align: right"]41088[/TD]
[TD]aksaggar[/TD]
[TD]821-7-01 NOI:TURN 1 OUT FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]21:47:35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akshay Aggarwal[/TD]
[TD="align: right"]41088[/TD]
[TD]aksaggar[/TD]
[TD]821-8-00 NOI:TURN 2 IN FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]22:19:24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Taufique Ahmad[/TD]
[TD="align: right"]37716[/TD]
[TD]taahmad[/TD]
[TD]821-8-00 NOI:TURN 2 IN FL7[/TD]
[TD="align: right"]20121108[/TD]
[TD="align: right"]14:26:49[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
On further look the answer for Akshay for 08 Nov 2012 "IN" should be 02:20:28 and for Taufique it should be 14:26:49 which is the first time IN
 
Upvote 0
Thanks Andrew, much obliged. Have been able to figure out where I was going wrong. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
L
  • Solved
Calculate start time from end time with multiple times per day for each ID
2
Replies
10
Views
274
Fluff
Fluff
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
586
Alex Blakenburg
A
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
393
freeb1893
F

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top