Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
I'd appreciate some help please as I'm very rusty with function
manipulation. The files I want to process contain GPS data representing walks (hikes). They contain several hundred text lines ('trackpoints') showing details of location co-ordinates and times. The times look like this: 07:57:12 07:57:48 07:58:31 etc My aim is to identify trackpoints for which the time gap before the NEXT trackpoint is greater than say 5 minutes. These represent where I stopped, had lunch, stepped into a pub, etc, which can be very time consuming to find manually. So far my best effort is as follows, but it's very kludgy and doesn't work properly anyway: 1. Edited the data first in my text editor so that it contained only the times in the form above, not all the other fields. 2. Imported that file into Excel 2000, specifying a colon as the field separator. Hour Min Sec ----- --- --- 7 57 12 7 57 48 7 58 31 etc 3. Converted the Hrs/Mins to get Total Seconds. 4. Rounded that back to minutes, the column 'Gap (mins)'. 5. Used the formula =IF(F555,"Yes") in another column to decide if the gap was longer than 5 mins. 6. Added a 'Line' column so that I will be able to identify the trackpoint in the original file after sorting my Excel data. SIDE-QUESTION: What is a simple way to create that succession 1, 2, 3, 4, etc please? I can do it by typing the 1 and the 2 and then dragging those down with my mouse - but as soon as I drag below the bottom screen edge it becomes uncontrollable, and I get thousands of unwanted entries. Is there a simple keystroke method? Or some way of slowing the mouse's frenzy? So at this stage the worksheet looks like this: Hour Min Sec Total Gap Gap Mins Line Secs Secs Mins over 5? ----- --- --- ----- ---- ---- ------- ---- 7 57 12 28632 1 7 57 48 28668 36 1 FALSE 2 7 58 31 28711 43 1 FALSE 3 7 58 56 28736 25 0 FALSE 4 etc 7. Sort that to get all those showing 'Yes' instead of FALSE at the top. That almost worked, but there's still clearly some flaw connected with the FIRST result: https://dl.dropbox.com/u/4019461/Excel-Gaps-1.jpg In any case, it's a long way short of the ideal, which would be to show ONLY the lines matching my criterion, preferably with the all original fields present as well. But I'd cheerfully settle for getting my long-winded method fixed please! -- Terry, East Grinstead, UK |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill time schedule with gaps EDIT | New Users to Excel | |||
Autofill time schedule with gaps | New Users to Excel | |||
Displaying time from 00:00 to 0.00 | New Users to Excel | |||
Displaying Unix time as date and time | Excel Discussion (Misc queries) | |||
Transpose Column With Gaps to Row With No Gaps? | Excel Discussion (Misc queries) |