Not really sure whether this'll work,
but maybe worth a play ..
Assume your data as posted is split into 3 cols,
data from row2 down:
col A = date
col B = time
col C = text (various)
Assume that there'll be no more than 2 consecutive rows
of ".. some text between the Dialings .." (as per sample data)
Put in D2:
= --ISERROR(SEARCH(C2,"DIAL 1-800-827-1159"))
Put in E4:
=IF(AND(D1=0,D2=1,D3=1,D4=0),B4-B1,IF(AND(D2=0,D3=1,D4=0),B4-B2,""))
Copy E4 down
(Leave E2, E3 empty)
Format col E as time
Col E seems to return what you're after*
(for the sample data-set at least)
*time between successful sendings
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Donnie" wrote in message
...
Hi - I need a little help devising something that will
basically do the following:
I have a set of dates of transmissions and I want to find
the time between successful transmits. See below:
11/11/04 6:01 DIAL 1-800-827-1159
11/11/04 6:02 Nothing was received.
11/11/04 6:04 DIAL 1-800-827-1159
11/11/04 6:07 DIAL 1-800-827-1159
11/11/04 6:10 DIAL 1-800-827-1159
11/11/04 6:14 DIAL 1-800-827-1159
11/11/04 6:17 DIAL 1-800-827-1159
11/11/04 6:18 Nothing was received.
11/11/04 6:20 DIAL 1-800-827-1159
11/11/04 6:24 DIAL 1-800-827-1159
11/11/04 6:25 Nothing was received.
11/11/04 6:27 DIAL 1-800-827-1159
11/11/04 6:29 2 file(s) received.
11/11/04 6:29 Transmitted Records Deleted from Output
Queues
11/11/04 6:31 DIAL 1-800-827-1159
11/11/04 6:35 DIAL 1-800-827-1159
11/11/04 6:38 DIAL 1-800-827-1159
You can see that if a transmission is considered
successful, there is some text between the Dialings
(nothing was recieved, 2 files recieved, transmitted
records deleted..., etc). Only when there is nothing
between Dialings was it unsuccesful. How could I go about
creating something that would find the time between
successful sendings? Any help is greatly appreciated.
Donnie.
|