Time stamp differents
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
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
Last Comment
ASKER
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
I am getting an error on the formula sent to me.
Thank you
I encountered an error when downloading the file. Please check that it is an ASCII text file and not some binary file.
ASKER
the zip file worked. thanks.
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
TEST.zip
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for your quick response to my question and adding the solution to the log file.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
This formula will give the time difference between A1 and A2
=(VALUE(REPLACE(A2,1,FIND(
and this formula will throw a message if the difference between D1 and D2 is not 1
=IF(VALUE(SUBSTITUTE(REPLA