Avatar of nedbacan
nedbacan
Flag for United States of America asked on

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
Microsoft ExcelMicrosoft DevelopmentMicrosoft Applications

Avatar of undefined
Last Comment
nedbacan

8/22/2022 - Mon
Saqib Husain

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","")
nedbacan

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
aikimark

I encountered an error when downloading the file.  Please check that it is an ASCII text file and not some binary file.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
nedbacan

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

I will resend it ,
TEST.txt
TEST.zip
aikimark

the zip file worked.  thanks.
aikimark

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
nedbacan

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.
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
nedbacan

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