Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old December 4th 08, 02:47 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 527
Default Program find a 5 minute gap

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





  #12   Report Post  
Old December 4th 08, 02:56 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 55
Default Program find a 5 minute gap

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






  #13   Report Post  
Old December 4th 08, 03:07 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default Program find a 5 minute gap

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












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find the correct program to download an .xls attachment sportshistorybuff Excel Discussion (Misc queries) 2 June 30th 06 04:49 PM
Can you find me a cash out program for a restaurant / bar? GP007 Excel Discussion (Misc queries) 1 November 3rd 05 01:36 PM
convert time from 60 minute hour to 100 minute hour Jboerding Excel Discussion (Misc queries) 2 July 6th 05 11:30 PM
How do I activate the trial exel 2003 program if it does't find c. Dolores Dueck New Users to Excel 1 January 3rd 05 02:19 AM
convert 100 minute hour to a 60 minute hour Todd Excel Worksheet Functions 1 November 15th 04 06:14 PM


All times are GMT +1. The time now is 11:18 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017