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

Extract first and last login time for each day only
 

hi,

data are located in 3 columns?

--
isabelle


Le 2012-10-12 10:16, 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

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

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

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.

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.

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.

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