time reference

kxlvba

New Member
I have the below mentioned data. Here the Name heading starts from A1. I need a formula which would return the least of in time and the max of out time. This needs to be done for each name like for Jack and Jill seperately. Is there any formula with index, match which should help me achieve this. Here, after sorting for each name the first corresponding row will have the least of intime and the last row for the corresponding name will have the max out time.

Name Id Date In Time Out Time Logout Date
Jack 99554 10/3/2006 9:29:36 AM 10:07:16AM 10/3/2006
Jack 99554 10/3/2006 10:07:16 AM 10:09:21AM 10/3/2006
Jack 99554 10/3/2006 10:09:21 AM 11:23:28AM 10/3/2006
Jack 99554 10/3/2006 11:23:28 AM 11:38:12AM 10/3/2006
Jack 99554 10/3/2006 11:38:12 AM 6:37:38PM 10/3/2006
Jill 98542 10/3/2006 3:30:15 AM 5:28:31AM 10/3/2006
Jill 98542 10/3/2006 5:28:31 AM 5:38:07AM 10/3/2006
Jill 98542 10/3/2006 5:38:07 AM 6:18:50AM 10/3/2006
Jill 98542 10/3/2006 6:18:50 AM 12:31:40PM 10/3/2006

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Jon von der Heyden

MrExcel MVP, Moderator
Sound like you need an array formula. Something like:

=MIN(IF(A1:A100="Jack",B1:B100))
confirmed with control + shift + enter
for MIN in time

and

=MAX(IF(A1:A100="Jack",C1:C100))
confirmed with control+shift+enter
For MAX out time

and you could change "Jack" to a cell reference instead too.

Hope this makes sense. Good luck!

Andrew Poulsom

MrExcel MVP
If your list is sorted like that and in the range A1:F10, make a list of names starting in say G2. In H2 enter (for earliest time):

=INDEX(\$D\$2:\$D\$10,MATCH(G2,\$A\$2:\$A\$10,0))

In I2 enter (for latest time):

=INDEX(\$E\$2:\$E\$10,MATCH(G2,\$A\$2:\$A\$10,0)+COUNTIF(\$A\$2:\$A\$10,G2)-1)

and copy those formulas down.

Replies
8
Views
114
Replies
1
Views
254
Replies
2
Views
430
Replies
3
Views
100
Replies
2
Views
160

1,147,747
Messages
5,742,966
Members
423,769
Latest member
LongToast

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.

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

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