I am doing research for my thesis. This involved putting a survey on
the Internet. Some people responded to the survey in less than 30
seconds, and I want to delete these participants because they probably
did not read the questions.

Column A1 lists when they began the survey and column A2 lists when
they finished the survey:

A1 A2
August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM
August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM
August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM
August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM
August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM


I would like to identify cases where the partipant took less than 30
seconds, so the output might have 1s if it's okay and 0s if it
doesn't:

A1 A2
A3
August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM 1
August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM 1
August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM 1
August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM 0
August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM 1

Is there any way to do this? I realize that it's complicated but I
have 2000 participants and I don't want to do it by hand!

Thank you!


--
LittleAndLost
------------------------------------------------------------------------
LittleAndLost's Profile: http://www.excelforum.com/member.php...o&userid=16010
View this thread: http://www.excelforum.com/showthread...hreadid=274767

Hi!

It's somewhat complicated but probably not for the reason
you're thinking.

Just looking at those "date/times" tells me that they're
not real Excel dates/times but rather TEXT strings. If
they were real dates/times then it would be a simple
matter of just subtracting A1 from B1.

What you could do is to use the TEXT TO COLUMNS menu
command and separate out the actual times into another
column. I did this and separated the string like this:

column A column B column D column E
August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM

Now, you can use a formula like this:

=IF(E1-B1+(B1E1)<TIME(0,0,30),0,1)

Post back if you need help on using Text to Columns.

Biff

-----Original Message-----

I am doing research for my thesis. This involved putting
a survey on
the Internet. Some people responded to the survey in
less than 30
seconds, and I want to delete these participants because
they probably
did not read the questions.

Column A1 lists when they began the survey and column A2
lists when
they finished the survey:

A1 A2
August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27 PM
August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04 PM
August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22 PM
August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32 PM
August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16 PM


I would like to identify cases where the partipant took
less than 30
seconds, so the output might have 1s if it's okay and 0s
if it
doesn't:

A1 A2
A3
August 19, 2004, 7:19:35 PM August 19, 2004, 7:23:27
PM 1
August 19, 2004, 7:45:47 PM August 19, 2004, 7:47:04
PM 1
August 19, 2004, 8:30:31 PM August 19, 2004, 8:31:22
PM 1
August 19, 2004, 8:44:12 PM August 19, 2004, 8:44:32
PM 0
August 19, 2004, 8:46:12 PM August 19, 2004, 8:47:16
PM 1

Is there any way to do this? I realize that it's
complicated but I
have 2000 participants and I don't want to do it by hand!

Thank you!


--
LittleAndLost
----------------------------------------------------------
--------------
LittleAndLost's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=16010
View this thread:
http://www.excelforum.com/showthread...hreadid=274767

.

Assuming that the times are Excel numbers rather than text strings you
could use a formula along the lines of:

IF(End Time Cell - Start Time Cell < TIME(0,0,30),1,0)

Copy this down and cases where less than 30 seconds ar taken show a 1
in this column. You can autofilter based on this result to exclude the
cases taking less than 30 seconds.


--
gatwickxx
------------------------------------------------------------------------
gatwickxx's Profile: http://www.excelforum.com/member.php...o&userid=15613
View this thread: http://www.excelforum.com/showthread...hreadid=274767