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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
423
dshtesy
D
L
  • Solved
Calculate start time from end time with multiple times per day for each ID
2
Replies
10
Views
315
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
527
XLfriend777
XLfriend777
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
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
398
freeb1893
F

Forum statistics

Threads
1,224,828
Messages
6,181,214
Members
453,024
Latest member
Wingit77

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