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

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

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

Here is some actual data from the cell. You can see the third one down is
more than 5 minutes from the one above it. It is close to a 6 hour
difference. If I use a formula, these come in each day and I might have to
apply that formula daily. This is why I was thinking a module.

2008-12-02 9:15:02.900
2008-12-02 9:15:06.700
2008-12-02 3:54:41.400
2008-12-02 3:58:16.300
2008-12-02 4:04:31.400
2008-12-02 4:04:32.800
2008-12-02 4:05:15.000
2008-12-02 4:12:42.900


"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

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

OK, silly question, how do you apply to a range?


"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

You select the range (say A1:A20) ; apply conditional formatting with
formula the same as if you had selected only the first cell (A1).
Or use the format painter
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Striker" wrote in message
...
OK, silly question, how do you apply to a range?


"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

I must be thinck, all this does is highlight the entire range no matter the
time difference.




"Bernard Liengme" wrote in message
...
You select the range (say A1:A20) ; apply conditional formatting with
formula the same as if you had selected only the first cell (A1).
Or use the format painter
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Striker" wrote in message
...
OK, silly question, how do you apply to a range?


"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

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

Maybe the issue is format related. The range is formatted as text if this
matters. If I try to change to date or time, it is not recognized, the data
remains the same no display change.


"Striker" wrote in message
...
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

Try this in conditional format

Formula is:

=MINUTE(G2)-MINUTE(OFFSET(G2,1,0))=5

this worked for me.

Peter Atherton

"Striker" wrote:

Here is some actual data from the cell. You can see the third one down is
more than 5 minutes from the one above it. It is close to a 6 hour
difference. If I use a formula, these come in each day and I might have to
apply that formula daily. This is why I was thinking a module.

2008-12-02 9:15:02.900
2008-12-02 9:15:06.700
2008-12-02 3:54:41.400
2008-12-02 3:58:16.300
2008-12-02 4:04:31.400
2008-12-02 4:04:32.800
2008-12-02 4:05:15.000
2008-12-02 4:12:42.900


"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

Well I finally did get it to work with this =F3-F2 1/24/60*5

However you can't apparently use the Greater than conditional format, you
have the use the formula option. At least thats the only thing I changed,
and it worked.

Thanks for all the help.


"Billy Liddel" wrote in message
...
Try this in conditional format

Formula is:

=MINUTE(G2)-MINUTE(OFFSET(G2,1,0))=5

this worked for me.

Peter Atherton

"Striker" wrote:

Here is some actual data from the cell. You can see the third one down
is
more than 5 minutes from the one above it. It is close to a 6 hour
difference. If I use a formula, these come in each day and I might have
to
apply that formula daily. This is why I was thinking a module.

2008-12-02 9:15:02.900
2008-12-02 9:15:06.700
2008-12-02 3:54:41.400
2008-12-02 3:58:16.300
2008-12-02 4:04:31.400
2008-12-02 4:04:32.800
2008-12-02 4:05:15.000
2008-12-02 4:12:42.900


"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

Then firts convert to "real" Excel date and time:

=DATEVALUE(LEFT(G2,10))+TIMEVALUE(RIGHT(G2,13))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Striker" wrote in message
...
Maybe the issue is format related. The range is formatted as text if this
matters. If I try to change to date or time, it is not recognized, the
data remains the same no display change.


"Striker" wrote in message
...
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