Home 
Search 
Today's Posts 
#11




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. 20081202 9:15:02.900 20081202 9:15:06.700 20081202 3:54:41.400 20081202 3:58:16.300 20081202 4:04:31.400 20081202 4:04:32.800 20081202 4:05:15.000 20081202 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 [20081203 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




Program find a 5 minute gap
Well I finally did get it to work with this =F3F2 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. 20081202 9:15:02.900 20081202 9:15:06.700 20081202 3:54:41.400 20081202 3:58:16.300 20081202 4:04:31.400 20081202 4:04:32.800 20081202 4:05:15.000 20081202 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 [20081203 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




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 G2G20,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 =G2G11/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. 20081203 09:54:08.100 20081203 09:59:08.100 20081203 10:01:08.100 20081203 10:02:08.100 "Niek Otten" wrote in message ... It works for me. But you can use Conditional FormattingHighlight 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: =A2A11/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 [20081203 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 
Display Modes  


Similar Threads  
Thread  Forum  
How to find the correct program to download an .xls attachment  Excel Discussion (Misc queries)  
Can you find me a cash out program for a restaurant / bar?  Excel Discussion (Misc queries)  
convert time from 60 minute hour to 100 minute hour  Excel Discussion (Misc queries)  
How do I activate the trial exel 2003 program if it does't find c.  New Users to Excel  
convert 100 minute hour to a 60 minute hour  Excel Worksheet Functions 