Extract first and last login time for each day only

[jc9972003]

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

 

[isabelle]

hi,

data are located in 3 columns?

--
isabelle


Le 2012-10-12 10:16, (e-mail address removed) a écrit :

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

 

[jc9972003]

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 saythis is for John, Doe Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39PM 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

 

[jc9972003]

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 saythis is for John, Doe Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39PM 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.

 

[isabelle]

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, (e-mail address removed) a écrit :

 

[isabelle]

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 :

 

[jc9972003]

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 saythis is for John, Doe Doe, John Sep 4, 2012 7:07:03 AM Sep 4, 2012 4:18:39PM 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