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.
0
12/10/2004 2:18:54 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
477 Views

Similar Articles

[PageSpeed] 58

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 <at>yahoo<dot>com
----
"Donnie" <donovan_d_balliet@yahoo.com> wrote in message
news:2a6f01c4de5e$98a26c50$a301280a@phx.gbl...
> 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.


0
demechanik (4694)
12/10/2004 7:58:57 AM
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 <at>yahoo<dot>com
----


0
demechanik (4694)
12/10/2004 12:45:08 PM
Reply:

Similar Artilces:

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

All Times in GMT
I recently switched from POP3 to IMAP handling of my e-mail, and now al incoming mail is marked with GMT time. (I'm in California and normall GMT +8). Apart from being strange, it screws up mail sorting becaus new incoming messages are placed halfway down my Inbox below messages received 7 hours ago. Also, when I "touch" a new incoming e-mail (click once on it withou opening it) the time changes to local time. What's up with that? My ISP swears it's not their problem. It must be some option or settin lurking within Outlook 2002. When I was using Outlook in POP3 mode ...

Formula to reference another worksheet, locate data, then record i
Hi All, It has been over 10 years since I did my Excel studies and I've unfortunately forgotten everything I haven't used regularly. My office has recently upgraded to Office 2007 (upgrade not being the descriptor I'd have chosen!) and I'm struggling with Excel. I've found my way around most issues, but I'm REALLY stuck now and suffering Friday-itis on top of it all! Essentially I have a multi-sheet workbook for my debtors ledger. Each page has separate columns for the customer numbers, names, total debts in each age range (7 days, 14 days etc), totals and lastl...

Time format #3
I have a cell which is formatted to time as hh:mm Now, the user has to type the time in the above format, hh:mm as above. Is it possible for me to allow the user to simply type in the time as hhmm and then for the spreadsheet to automatically format it to hh:mm. So for example, 08:30. I would like the user to be able to type in either 08:30 or 0830 and for the spreadsheet to recognise both entries and format it to 08:30. Any ideaS? Neil Not without using VBA See Chip Pearson's site for code. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben Excel MVP On Sun, 27 N...

Looking up data from Access
I would like to extract data from MS Access into Excel '97 to populate single cell, preferably imitating the 'vlookup' function, i.e. give the value of an Excel cell, the related field name in the externa database query, and the field with the value to return, it wil populate the current cell with this value. Not sure how clear that is. Let me know if it isn't, and thanks fo any help you can give -- Message posted from http://www.ExcelForum.com I've found a solution using the SQL.Request workbook function, whic does what I need. However, I need to populate many cells...

partially turning off auto calculate?
Any way to NOT auto caluculate 1 particular formula, while leaving the rest of excel to auto calculate? In particular, I have an SQL query I would prefer to run manually, while not interferring with the rest of my workbook(s) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Calculation is an all or nothing setting. You can't prevent calculation of specific ranges. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com ...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

data points #3
I have a scatter chart with the following info : sales price, square footage, and subdivision. With the x and y axis being sales price and square footage. There are some entries where i will have several sales in one subdivision and i would like all those points to show up in the same color. How do i get several points to show up in the same color instead of excel assigning each entry a new label? Include all the related points in the same data series, rather than as a different data series for each point. -- David Biddulph "affordablegreen" <affordablegreen@discussion...

Money 05 cuts off data
B of A (Washington) using Yodlee & msn to sync data. The problem I'm experiencing is that the Payee field is being cut off. I've used /using the Payee Rules Manager, which has been working to a certain extent. My question is... What is the size limit for the payee field? A typical transaction from bofa(WA) looks like "PURCHASE 5xx29xxx658xxxxx3 ON 12/30 AT CASTLE ICE RENTON WA" When synced as configured above all i get is a very small portion, maybe part of the actual name. Second question is when trying to do a manual export from bofa (Wa) website I have the opt...

Moving data between Excel sheets
"I have an excel spreadsheet that has 2 sheets, one has a list of open issues" and one a list of "closed issues". One of the fields on the "open issues" sheet has a "closed date" field. Whenever a date is typed into this field, I would like for it to be moved to the "closed issues" sheet, to avoid duplicate, manual entries being done. Can anyone help me with the best way to achieve this? Can this be achieved by using Macro's. Any help with this, would be greatly appreciated. Thanks! Suggest you send this to the programming newsgroup...

find data in multiple open workbooks
Im looking to find data in multiple workbooks that are all opened at once. Is there a way of doing this? ...

data labels from second column
Hi Column A is list of names (Bob, Sue, etc); column B is how much they collected (58, 12, etc); Column C is the date they did it on - so record 1 says Bob collected 58 on 10/07/07, for instance. I want to create a chart with the date for the x axis, amount collected as the y axis and data labels at each point within the graph giving the collector's name. So at X=12/07/07, y=58 I want it to say Bob within the chart area. Any help much appreciated. Regards Chandler On Mon, 3 Sep 2007, in microsoft.public.excel.charting, Chandler <Chandler@discussions.microsoft.com> said: &...

UTC time to Central Time (US)
Hello. I need to convert UTC time to US Central Time Zone. The date is in one column and the time is in another below. A | B ---------------------------------- Date | Time ---------------------------------- 2005-10-24 | 12:15:00 I want to convert the Date and Time columns in UTC to a single Date and Time column in US Central Time. Thanks for any advice Assuming UTC is the same as GMT then the difference is 6 hours STD, so if the time is 06:00 AM it is 12:00 PM GMT, thus add 6 hours and since the y are all numbers just add =A1+B1+(6...

Transfer data manually onto other database
Hello, I am needing to transfer data onto a Mysql database. I have had some advice and am trying to find a software solution. So main features are to be able to migrate large amounts of contacts and data every day. The migration should be done manually. Thanks a lot! Look here: http://www.kitebird.com/articles/access-migrate.html#TOC_6 and here: http://www.bullzip.com/products/a2m/info.php Regards Kevin "Guertas" <Guertas@discussions.microsoft.com> wrote in message news:DDFFC964-C78A-446E-A7AA-5F4E632FF20A@microsoft.com... > Hello, > >...

Data Validation #17
Hello Forum, up to now, I am working for a very long time on solving followin problem: I would like to insert in one cell a whole number between let's sa 1-596. There is a list with all numbers, which are already in the exce sheet, and should not be allowed to enter to the cell...it should b profed, if the number i want to enter from 1 to 596 is allready in use if so, there should come up a alert message. Is this possibe, probabl with a vlookup in data validation? What should i precice do? Thank you very much in advance, greetz Mteepe -- mteepe ---------------------------------------...

how to retrieve data from password protected web pages?
how can I retrieve data from password protected web pages in excel? ...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

Outlook hangs ,7th time :-(
Hi all, In our company we are using Exchange 2000 as mail server, and outlook 2002 sp2 as email client. Each client is configured to download its emails to a local pst file. We are facing the following issue : If you add a rule to move messages from inbox to a particular folder, during send/receive and just after receiving 3-5 messages (and these messages are moved to their folders) the outlook stops responding. If you run outlook with /safe parameter it will not hang! If any one could advise I would be gratefull. Thanks, Osama. ...

BYTE Data type and UNICODE
I am converting MFC application to UNICODE. Please inform if BYTE data type is UNICODE enabled. If not how can I convert it to UNICODE. Thanks for your reply Regards Alpha BYTE is typedef'd as unsigned char, so is independent of UNICODE. If you have used that type correctly (i.e. as an 8-bit unsigned integer, rather than as a character), you should not have to convert it. Keith MacDonald "Alpha Siera" <AlphaSiera@discussions.microsoft.com> wrote in message news:5C837E1E-AB84-4245-85EC-FD206491306E@microsoft.com... > I am converting MFC application to UNICODE. Pleas...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

Time Entry
A simple problem I would appreciate your help with. I have a work sheet with several cells where time is to be entered in the format hh:mm. Unfortunately a lot of users are enetering it in the format hh.mm. Is there any way I can automatically convert to the right format, or not accept the (.) but only (:). Thanks for your help. Private Sub Worksheet_Change(ByVal Target As Range) If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then MsgBox "invalid entry" End If End Sub Enter the above code in the sheets module Mangesh -- mangesh_yadav -------------------...

Automatically update Value for data label
Hello I am using Excel 2003 SP2, and have some graphs which have the value (data label) for the last month. Each month new data is entered and the data label has to be deleted for the previous month and the data label for the most recent month added (it still uses the same old data - new data is only entered for the most recent month). Is there any way where the data label can automatically update with the most recent months value (as the chart updates itself automatically currently). Any ideas appreciated. Thank you in advance. Regards, Nav ...