time reference

[kxlvba]

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

 

[Jon von der Heyden]

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]

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.