Time stamp differents

nedbacan
nedbacan used Ask the Experts™
on
Dears Experts.

I have a log file that contains thousands of lines of data.

My intention is to import it into Excel and examine the data, and perform two different checks.  

Is there a macro or can one be create to check each record date and assure each records time interval are all the same and the sequence also.  
   
Check 1:  Assure each record are 2 minutes apart or the number of seconds user assigned each record to be.
For instance,  Each record should be 2 minutes (120 seconds) aparts.
Record Number = 1,1/1/2012 2:00:00 PM
Record Number = 2,1/1/2012 2:02:00 PM
Record Number = 3,1/1/2012 2:04:00 PM
Record Number = 4,1/1/2012 2:06:00 PM

Check 2:
In the log, the column for Sensor Blinks should all be in sequential order.  
Flag each line skip. For instance the following example below, Sensor Blink 5 was skipped, so it needs to be reported.
Sensor Blinks = 1,
Sensor Blinks = 2,
Sensor Blinks = 3,
Sensor Blinks = 4,
Sensor Blinks = 6,
Sensor Blinks = 7,
TEST.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If the cell contents are what you have shown above then

This formula will give the time difference between A1 and A2
=(VALUE(REPLACE(A2,1,FIND(",",A2),""))-VALUE(REPLACE(A1,1,FIND(",",A1),"")))*24*60

and this formula will throw a message if the difference between D1 and D2 is not 1
=IF(VALUE(SUBSTITUTE(REPLACE(D2,1,FIND("=",D2),""),",",""))-VALUE(SUBSTITUTE(REPLACE(D1,1,FIND("=",D1),""),",",""))<>1,"Not consecutive","")

Author

Commented:
Hi ..I was wondering if you can send me the excel copy of your result with the log I attached.

I am getting an error on the formula sent to me.

Thank you
Top Expert 2014

Commented:
I encountered an error when downloading the file.  Please check that it is an ASCII text file and not some binary file.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I am sorry but it's an ascii test file.  The extension is txt.

I will resend it ,
TEST.txt
TEST.zip
Top Expert 2014

Commented:
the zip file worked.  thanks.
Top Expert 2014

Commented:
In the attached zip file, I've placed an Excel workbook into which I imported your txt file, as comma-delimited text.  I removed the " UTC" characters from the second column cell values and added a new column A.  The formulas in column A are the number of seconds between successive time stamp values in column C (formerly B)
TEST.zip

Author

Commented:
Good job !!!

I am not good in working with numbers but  what is " 86400 " referring to.


The second check was not added in the spreadsheet??  Checking the Sensor Blinks sequence.
Top Expert 2014
Commented:
86400 is the number of seconds in a day.

I parsed the sequence column and added a difference check to the attached workbook.  To help you get a feel for it, I also added column headers to the two difference columns and filtered for a sequence<>1.
TEST.zip

Author

Commented:
Thank you for your quick response to my question and adding the solution to the log file.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial