Deleting/IDing Rows that Don't Meet Criteria

  • Thread starter Thread starter LittleAndLost
  • Start date Start date
L

LittleAndLost

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

Column A1 lists when they began the survey and column A2 lists whe
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 3
seconds, so the output might have 1s if it's okay and 0s if i
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
have 2000 participants and I don't want to do it by hand!

Thank you
 
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+(B1>E1)<TIME(0,0,30),0,1)

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

Biff
 
Assuming that the times are Excel numbers rather than text strings yo
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
in this column. You can autofilter based on this result to exclude th
cases taking less than 30 seconds
 
Back
Top