time reference

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,022
Members
410,647
Latest member
bernardazar
Top