Removing time stamps that are less then "x" seconds after the previous time stamp

See if this helps.

Add a Helper column and use the formula

Formula:

Please Login or Register  to view this content.

in row 3 and copied down.

Then filter for the lowest values you require.

Hi Jim,

may not be the neatest but please see attached spreadsheet,
I have changed the formatting of the time (New column Q) (column O can be deleted) to use a time formula and then created an IF statement in column S.

To remove the errors you can filter column S by the "1" and delete the rows.

If you want an explanation of the formulas, please let me know.

Apologies, please use the formula below in column S:

Formula:

Please Login or Register  to view this content.

with the start and end times in format: hh:mm:ss.000000
start time in A2
end time in B2

use this formula:

Please Login or Register  to view this content.

sample file attached

Please Login or Register  to view this content.

Wow, thanks everyone that replied. The support was awesome, really appreciate the time each one of you put in!
I really like seeing all the different ways it could possibly be solved.

janmorris
Thanks heaps for that very complicated looking formula. I really like it as i can visually see the time difference between points which is interesting. Only thing i noticed is when it cross's the 12 hour mark it throws a funny number. It really is not a big deal as i can just sort through each one at every 12th hour but i thought if there is a simple change of formula that would be cool. But don't expect you to spend time on it again. What you have done so far is great.

KOKOSEK
Thanks very much for your help. I'm no expert with excel but I'm guessing yours is some sort of macro? I would love to give yours a go but not exactly sure on how to plug it in, i have only ever recorded my own macros in the past. If you could point me in the right direction that would be great thanks.
Should of had a better google first. Think Ive got it sorted. Very cool. Thanks!


Thanks again everyone!


Jim.

Just want to say these worked like a treat. I wanted to make a macro out of it anyway so I used KOKOSEK's suggestion. What would take me hours of crosschecking again and again just took me a few minutes to go through. You have made my life so much easier. Thanks Mate!

Because sometimes we change the duration, am i able to simply change the "00:00:35" in the code to what ever i like. Thats all i have to do?

Thanks for the rep.

Because sometimes we change the duration, am i able to simply change the "00:00:35" in the code to what ever i like. Thats all i have to do?

Yep, that's it.

@Navara32

do you get some kind of error?
can you give more detail on the anomaly?

in my testing...
with a start time of 01:38:26.677483
and an end time of 23:38:26.790675
i get a result of 79200.113192

and as there are 86400 seconds in a day, and 3600 seconds in an hour (7200 seconds in 2 hours), this is correct.

im working on Mac and Google Sheets, which give identical results.

---

to explain the formula a little bit...
"TIME" ensures the numbers are treated as parts of time, and not as raw numbers (eg. 6 hours and 30 minutes can be given as 6.5 hours, or 6:30)
"MID" and "RIGHT" pulls a certain number of characters from a certain position
"*24*60*60" is for the hours, minutes, seconds
"/1000000" is for the microseconds

Happy to help.