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