Displaying time gaps

[Terry Pinnell]

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(F55>5,"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 Pinnell]

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

A few minutes after posting I realised that I should have used the Paste >
Special > Values command on all my worksheet before doing the sort. So
that solves that aspect, and the result now looks as I'd expected:

https://dl.dropbox.com/u/4019461/Excel-Gaps-2.jpg

Which leaves:

1. The side-issue about how to reliably create number sequences.

2. Could I avoid the sort and instead somehow get Excel to delete the
entire line if my condition is not met?

3. Is there a smarter/faster/neater method?

 

[GS]

Terry Pinnell has brought this to us :

A few minutes after posting I realised that I should have used the Paste >
Special > Values command on all my worksheet before doing the sort. So
that solves that aspect, and the result now looks as I'd expected:

https://dl.dropbox.com/u/4019461/Excel-Gaps-2.jpg

Which leaves:

1. The side-issue about how to reliably create number sequences.

2. Could I avoid the sort and instead somehow get Excel to delete the
entire line if my condition is not met?

3. Is there a smarter/faster/neater method?

Have a look at the MINUTE() function. It will let you work directly
with time values, and so you won't have to fiddle around with parsing
h/m/s!

Example:
Times listed in colA, starting in row2...
In B3: =MINUTE($A3-$A2)

This will return an integer between 0 and 59. If you don't want to
include time differences under 5 minutes then...


In B3: =IF(MINUTE($A3-$A2)>5,MINUTE($A3-$A2),"")

...which will leave the cell empty.

Alternatively, you could use ConditionalFormatting to 'flag' cells if
you don't want to use a dedicated column for this. Just select all the
cells in colA EXCEPT A1, open the CF dialog and use 'Formula' and type
the following into the box...

=MINUTE($A3-$A2)>5

...and set the font or fill to change as desired.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Terry Pinnell]

Terry Pinnell has brought this to us :

Have a look at the MINUTE() function. It will let you work directly
with time values, and so you won't have to fiddle around with parsing
h/m/s!

Example:
Times listed in colA, starting in row2...
In B3: =MINUTE($A3-$A2)

This will return an integer between 0 and 59. If you don't want to
include time differences under 5 minutes then...


In B3: =IF(MINUTE($A3-$A2)>5,MINUTE($A3-$A2),"")

..which will leave the cell empty.

Alternatively, you could use ConditionalFormatting to 'flag' cells if
you don't want to use a dedicated column for this. Just select all the
cells in colA EXCEPT A1, open the CF dialog and use 'Formula' and type
the following into the box...

=MINUTE($A3-$A2)>5

..and set the font or fill to change as desired.

Many thanks, Garry, looking forward to trying that later today.

 

[GS]

Many thanks, Garry, looking forward to trying that later today.

Ok! If you need to trap time difference at 5 mins or longer then...

change

5 to
=5

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Terry Pinnell]

Ok! If you need to trap time difference at 5 mins or longer then...

change

Successfully implemented your first two methods, thanks!

But having some difficulty with the CF method.

First, I don't see any 'Formula' option in the dialog.

https://dl.dropbox.com/u/4019461/Excel-Gaps-CF-1.jpg

If I proceed to use the dialog my formatting options seem restricted
merely to bold or italic:

https://dl.dropbox.com/u/4019461/Excel-Gaps-CF-2.jpg

And if I try the Pattern tab and try to apply red shading, it gets applied
regardless of the condition:

https://dl.dropbox.com/u/4019461/Excel-Gaps-CF-3.jpg

 

[GS]

The 'Formula' option is in the dropdown at the top left of the dialog.

The font options are rather limited but sufficient for 'flagging'
purposes so those cells 'stand out' from others. Pattern is what to use
if you want highlighting, but masks gridlines and so you may want to
use the 'dotted' border style to put a 'box' around cells to simulate
the gridlines.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion