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:
FIRST_NAMEEMPLOYEELDAP_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>

<tbody>
</tbody>

The data is required in the following format
DateTotal HoursAkshay AggarwalTaufique Ahmadl
In08-Nov-12hh:mm:ss
Out08-Nov-12
hh:mm:ss

<tbody>
</tbody>
Total Hours
hh:mm:ss (Out - In)

<tbody>
</tbody>
In09-Nov-12
Out09-Nov-12
Total Hours
In10-Nov-12
Out10-Nov-12
Total Hours

<tbody>
</tbody>

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
DetailsDateTotal HoursAkshay AggarwalTaufique Ahmad
IN41221hh:mm:ss=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))))))=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))))))
OUT41221hh:mm:ss
Total Hourshh:mm:ss (Out - In)
In41222=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))))))=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))))))
Out41222
Total Hours
In41223=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))))))=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))))))
Out41223
Total Hours

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
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

ABCDEF
NameEMPLOYEELDAP_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

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
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

K
  • 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
189
XLfriend777
XLfriend777
F
  • 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
251
freeb1893
F
J
  • Solved
Calculating if Meal Allowance applies based on time out of the office
Replies
4
Views
380
ldonkers
L
P
  • Solved
Count Times entry between hour
Replies
7
Views
510
pbhange
P
M
  • Question
Worked time between multiple dates
Replies
1
Views
526
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,819
Messages
6,127,049
Members
449,356
Latest member
tstapleton67

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