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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
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? -- Terry, East Grinstead, UK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
Terry Pinnell has brought this to us :
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? 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
GS wrote:
Terry Pinnell has brought this to us : 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? 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. -- Terry, East Grinstead, UK |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
on 6/14/2012, Terry Pinnell supposed :
GS wrote: Terry Pinnell has brought this to us : 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? 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. 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
GS wrote:
on 6/14/2012, Terry Pinnell supposed : GS wrote: Terry Pinnell has brought this to us : 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? 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. Ok! If you need to trap time difference at 5 mins or longer then... change 5 to =5 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 -- Terry, East Grinstead, UK |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying time gaps
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 |
Reply |
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) |