Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Login Logout Date Problem
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 :( -- ascool_asice ------------------------------------------------------------------------ ascool_asice's Profile: http://www.excelforum.com/member.php...o&userid=23840 View this thread: http://www.excelforum.com/showthread...hreadid=374924 |
#2
|
|||
|
|||
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) ascool_asice wrote: 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 :( -- ascool_asice ------------------------------------------------------------------------ ascool_asice's Profile: http://www.excelforum.com/member.php...o&userid=23840 View this thread: http://www.excelforum.com/showthread...hreadid=374924 -- Dave Peterson |
#3
|
|||
|
|||
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 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format problem | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions | |||
Excel 2002 date formulas problem | Excel Worksheet Functions | |||
problem with formatting cell to date format | Excel Worksheet Functions | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) |