Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Program find a 5 minute gap
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
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 |