Login Logout Date Problem

[ascool_asice]

Problem

How do I get the First login and Last logout time when the logout time
is next day.
Col C Col D Col F
Col H Col I
Agent Name Extn Login Time Logout Date

Moinuddin Khan 20146 8:58 AM 6:02 PM 5/28/2005
Varun Bhushan 20156 9:01 AM 6:02 PM 5/28/2005
Shubha Khampari 20104 8:28 AM 5:07 PM 5/28/2005
Joseph Fernandes 20088 8:59 AM 6:07 PM 5/28/2005
Nasreen Rahim 20231 1:08 PM 5:12 PM 5/28/2005
Hiren Sardesai 20155 6:10 AM 9:08 AM 5/28/2005
Hiren Sardesai 20155 12:17 PM 1:05 PM 5/28/2005
Hiren Sardesai 20155 1:31 PM 1:32 PM 5/28/2005
Hiren Sardesai 20155 5:21 PM 5:33 PM 5/28/2005
Jihan Menezes 20086 2:57 PM 6:09 PM 5/28/2005
Jihan Menezes 20086 6:29 PM 8:29 PM 5/28/2005
JIHAN MENEZES 20086 8:43 PM 12:43 AM 5/29/2005
Marcellina Monis 20077 6:55 AM 8:46 AM 5/28/2005
Marcellina Monis 20077 9:03 AM 10:26 AM 5/28/2005
Marcellina Monis 20077 10:27 AM 1:27 PM 5/28/2005
Marcellina Monis 20077 1:55 PM 5:24 PM 5/28/2005
Marcellina Monis 20077 5:47 PM 7:04 PM 5/28/2005

At the moment the result that I get is

Col AA Col AC Col AD
Agent Name Login Logout
Moinuddin Khan 8:58 18:02
Varun Bhushan 9:01 18:02
Shubha Khampari 7:59 17:07
Joseph Fernandes 8:59 18:07
Nasreen Rahim 13:08 17:12
Hiren Sardesai 6:10 17:33
JIHAN MENEZES 14:57 20:29
Marcellina Monis 6:55 19:04


If you have a look Jihan's logout time should show 12:43 AM however it
shows me 20:29... Please help.

The formula that I have used to get the 1st login time is
{=1/MAX((AA25=$C$2:$C$402)*($F$2:$F$402<>0)*(1/$F$2:$F$402))}

The Formula that I have used to get the last logout time is
{=MAX(($C$2:$C$402=AA25)*($H$2:$H$402))}


Please Help

 

[Dave Peterson]

I think I would use a simple =vlookup() formula to get the first login time.

With my table in C3:G19, and my name in AA3:
=VLOOKUP(AA3,$C$3:$G$19,3,FALSE)

And this formula to get the last entry:
=LOOKUP(2,1/(AA3=$C$3:$C$19),$F$3:$F$19)

 

[Ola Sandström via OfficeKB.com]

This is one option:

AA: 'Names'
AC: =MIN(IF($AA25=$C$2:$C$402,F$2:F$402))
AD: =MAX(IF($AA25=$C$2:$C$402,H$2:H$402--($H$2:$H$402<$F$2:$F$402)))

Ola Sandström


Result:
Moinuddin Khan 08:58 18:02
Varun Bhushan  09:01 18:02
Shubha Khampari 08:28 17:07
Joseph Fernandes08:59 18:07
Nasreen Rahim 13:08 17:12
Hiren Sardesai 06:10 17:33
JIHAN MENEZES 14:57 00:43
Marcellina Monis06:55 19:04