Program find a 5 minute gap
Sorry to ask, but can you be a little more specific? My range in column "G"
is G2-G20,000. Range is named LST
In conditional formatting when I select the range and apply the folumla
=G2+1/24/60*5. It highlights the entire range no matter the value.
If I do the same thing and appply this formula =G2-G11/24/60*5. Nothing is
gighlighted in the range no matter the values.
I know it must be me, but I don't understand what I am doing wrong.
2008-12-03 09:54:08.100
2008-12-03 09:59:08.100
2008-12-03 10:01:08.100
2008-12-03 10:02:08.100
"Niek Otten" wrote in message
...
It works for me. But you can use Conditional Formatting-Highlight Cell
rulesGreater than, and fill in: =A1+1/24/60*5 (for cell A2, and apply
this rule to the rest of your range)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Striker" wrote in message
...
Thanks, this does not appear to work with the data in the cell the way it
is represented.
Should I be using Conditional Formatting - Highlight Cells rules- Greater
than rule?
"Niek Otten" wrote in message
...
You don't need code.
Use Conditional Formatting with a formula:
=A2-A11/24/60*5 (for cell A2 and below)
and coose a highlight color
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Striker" wrote in message
...
Excel 2007 [Square Brackets] used to represent a Cell. They do not
exist in the data of the cell.
I have a column spreadsheet that has 15K records. IN column "G" all
records have a date/tame like [2008-12-03 09:54:08.100]. What I need
to do is search through that column to the end of the file and find any
gaps that may be 5 minutes or longer and highlight that row.
So the above, I need to search 09:54 and see if the next row has 09:59
or greater. If so highlight the row, if not move down one row and do
the same check. I'm looking to see if the row below is equal to or
greater than a 5 minute difference from the cell above.
Does this make any sense?
I am aware I will most likely need to do this in code. I am using
Excel 2007, and am somewhat familiar with modules, but have not worked
with them for a few years. Seems like I need a do until End of file
and a for each loop, but just a little lost on the details. Just got
Excel 2007, so it's new.
I also need to be careful to work with this workbook and this tab as
there will be many in the book.
Thanks for any help
|