Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Donnie
 
Posts: n/a
Default Calculating time between successful data transmissions

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.
  #2   Report Post  
Max
 
Posts: n/a
Default

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.



  #3   Report Post  
Max
 
Posts: n/a
Default

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

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


Copy D2 down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 4 December 1st 04 07:47 PM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 06:55 AM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 04:52 AM


All times are GMT +1. The time now is 01:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"