Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Extract first and last login time for each day only

Help
I export this from a diffrent source.
Its a login logout report. Sometimes agents login and out many times during the day. I would like help with a formula that would give just the first login and last loging let's say this is for John, Doe

Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39 PM
Doe, John Sep 4, 2012 4:19:26 PM Sep 4, 2012 4:19:35 PM
Doe, John Sep 5, 2012 7:19:34 AM Sep 5, 2012 9:43:37 AM
Doe, John Sep 5, 2012 9:43:45 AM Sep 5, 2012 3:19:37 PM
Doe, John Sep 5, 2012 3:19:47 PM Sep 5, 2012 3:19:53 PM
Doe, John Sep 5, 2012 3:20:20 PM Sep 5, 2012 3:20:28 PM

So for Sep 4 I just want to see 9/4/2012 7:07 AM - 4:19 PM
For Sep 5 I just want to see 9/5/2012 7:19 AM - 3:20 PM

Thank you
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Extract first and last login time for each day only

On Friday, October 12, 2012 9:16:54 AM UTC-5, (unknown) wrote:
Help I export this from a diffrent source. Its a login logout report. Sometimes agents login and out many times during the day. I would like help with a formula that would give just the first login and last loging let's say this is for John, Doe Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39 PM Doe, John Sep 4, 2012 4:19:26 PM Sep 4, 2012 4:19:35 PM Doe, John Sep 5, 2012 7:19:34 AM Sep 5, 2012 9:43:37 AM Doe, John Sep 5, 2012 9:43:45 AM Sep 5, 2012 3:19:37 PM Doe, John Sep 5, 2012 3:19:47 PM Sep 5, 2012 3:19:53 PM Doe, John Sep 5, 2012 3:20:20 PM Sep 5, 2012 3:20:28 PM So for Sep 4 I just want to see 9/4/2012 7:07 AM - 4:19 PM For Sep 5 I just want to see 9/5/2012 7:19 AM - 3:20 PM Thank you


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Extract first and last login time for each day only

On Friday, October 12, 2012 9:16:54 AM UTC-5, (unknown) wrote:
Help I export this from a diffrent source. Its a login logout report. Sometimes agents login and out many times during the day. I would like help with a formula that would give just the first login and last loging let's say this is for John, Doe Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39 PM Doe, John Sep 4, 2012 4:19:26 PM Sep 4, 2012 4:19:35 PM Doe, John Sep 5, 2012 7:19:34 AM Sep 5, 2012 9:43:37 AM Doe, John Sep 5, 2012 9:43:45 AM Sep 5, 2012 3:19:37 PM Doe, John Sep 5, 2012 3:19:47 PM Sep 5, 2012 3:19:53 PM Doe, John Sep 5, 2012 3:20:20 PM Sep 5, 2012 3:20:28 PM So for Sep 4 I just want to see 9/4/2012 7:07 AM - 4:19 PM For Sep 5 I just want to see 9/5/2012 7:19 AM - 3:20 PM Thank you



Yes Isabelle, the data is in three columns name all the login times during the day and all the logout times durning the day.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Extract first and last login time for each day only

hi,

array formulas, validate with ctrl + shift + enter

=MIN(IF(DAY(B1:B6)=4,B1:B6,""))
=MAX(IF(DAY(C1:C6)=4,C1:C6,""))

isabelle


Le 2012-10-15 08:30, a écrit :
Yes Isabelle, the data is in three columns name all the login times
during the day and all the logout times durning the day.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Extract first and last login time for each day only

a better way,

array formulas, validate with ctrl + shift + enter

=MIN(IF((A1:A6="Doe, John")*DAY(B1:B6)=4,B1:B6))
=MAX(IF((A1:A6="Doe, John")*DAY(C1:C6)=4,C1:C6))

isabelle



Le 2012-10-15 16:06, isabelle a écrit :
hi,

array formulas, validate with ctrl + shift + enter

=MIN(IF(DAY(B1:B6)=4,B1:B6,""))
=MAX(IF(DAY(C1:C6)=4,C1:C6,""))

isabelle


Le 2012-10-15 08:30, a écrit :
Yes Isabelle, the data is in three columns name all the login times
during the day and all the logout times durning the day.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Extract first and last login time for each day only

On Friday, October 12, 2012 9:16:54 AM UTC-5, (unknown) wrote:
Help I export this from a diffrent source. Its a login logout report. Sometimes agents login and out many times during the day. I would like help with a formula that would give just the first login and last loging let's say this is for John, Doe Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39 PM Doe, John Sep 4, 2012 4:19:26 PM Sep 4, 2012 4:19:35 PM Doe, John Sep 5, 2012 7:19:34 AM Sep 5, 2012 9:43:37 AM Doe, John Sep 5, 2012 9:43:45 AM Sep 5, 2012 3:19:37 PM Doe, John Sep 5, 2012 3:19:47 PM Sep 5, 2012 3:19:53 PM Doe, John Sep 5, 2012 3:20:20 PM Sep 5, 2012 3:20:28 PM So for Sep 4 I just want to see 9/4/2012 7:07 AM - 4:19 PM For Sep 5 I just want to see 9/5/2012 7:19 AM - 3:20 PM Thank you


Thank you Isabelle
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pull login time and windows username Boss Excel Programming 4 June 24th 09 07:29 AM
Employee login time sheet widman Excel Discussion (Misc queries) 2 May 17th 09 03:10 PM
Login date & time to the VBA application. [email protected] Excel Programming 2 July 5th 07 03:25 PM
Tracking changes of login & time Ozgur Pars[_2_] Excel Programming 8 April 19th 06 05:13 PM
how do I set up an employee time in/out login kordahl Excel Programming 1 April 18th 06 12:41 PM


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"