Program find a 5 minute gap

[Striker]

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

 

[Niek Otten]

You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

 

[Striker]

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?

You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Striker]

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

 

[Niek Otten]

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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

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?

You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Striker]

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

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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

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?

You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Bernard Liengme]

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

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

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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

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?


You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Striker]

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

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

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

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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


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?


You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Striker]

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-G1>1/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

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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

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?

You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Striker]

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.

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-G1>1/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

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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

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?


You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Billy Liddel]

Try this in conditional format

Formula is:

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

this worked for me.

Peter Atherton

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

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

 

[Striker]

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.

Try this in conditional format

Formula is:

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

this worked for me.

Peter Atherton

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

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

 

[Niek Otten]

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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.

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-G1>1/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

It works for me. But you can use Conditional Formatting-Highlight Cell
rules>Greater 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


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?


You don't need code.
Use Conditional Formatting with a formula:

=A2-A1>1/24/60*5 (for cell A2 and below)

and coose a highlight color

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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