Calculating time between successful data transmissions

[Donnie]

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.

 

[Max]

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

 

[Max]

Sorry, just realised there's a missing line after:

Put in D2:
= --ISERROR(SEARCH(C2,"DIAL 1-800-827-1159"))

Copy D2 down