Microsoft Excel - How to conditionally format cells

Asked By Robert Trowbridge on 07-Jan-15 02:40 PM
Good afternoon all;
I am using Excel 2010, and have imported a report from Access database that includes a great deal of text and times, and I want to conditionally highlight values greater than 5 minutes. I have noticed that the description of the cells (provided by what I see when opening the imported workbook) says that they are text, but they display as shown below. I do not fully understand what that means, but it may be relevant.
Whenever I attempt to use the conditional formatting tool for "greater than" values it automatically highlights every cell in the list. When using the tool I enter values I am looking for as anything greater than 00:05:00. I have scoured the web for anything relevant with no success. I have also changed the format of the cells to number, time, text, and so on, but always achieve the same result.

The below list is the result of time intervals that were established in access. The times that these intervals were based on appear as (00-Jan-00) before formatting to display as times. The list looks like this (with the actual time to the left, and the interval on the right:

00-Jan-00 04:33:39
00-Jan-00 00:06:15
00-Jan-00 00:08:57
00-Jan-00 00:06:01
00-Jan-00 00:10:23
00-Jan-00 00:10:30
00:47:59
00-Jan-00 00:16:06
00:13:01
00:38:05
00-Jan-00 00:39:52

Thank you all for your help,
Robert
Harry Boughen replied to Robert Trowbridge on 07-Jan-15 10:43 PM
Hello Robert, This is going to be difficult because my return button does not seem to be working.  Your test for conditional formatting should be that is TIMEVALUE(B1) > 0.003472.  If you want the time value to be adjustable you could use number of minutes/60/24.  This is because time in timevalue values is expressed as a fraction of a day.  Hope this helps.  Harry
Robert Trowbridge replied to Harry Boughen on 09-Jan-15 02:34 PM
Harry; Thank you for your reply. However, I think I'm still not doing something right. Let me tell you my process, and maybe you can help me determine what I am doing wrong.
I choose New Rule (Under the conditional formatting option),
then "Use Formula to Determine which Cells to format",
Then I entered your formula in the box "Format Values Where this Formula is True. Then it highlights every cell.
The formula looks like this:
TIMEVALUE($I$6324:$I$6347) > 0.003472
Thank you again for your help. Robert
Robert Trowbridge replied to Harry Boughen on 09-Jan-15 03:52 PM
I think I may have found an issue I did not see before I asked my question.
It appears as though you must have a cell to reference in order to conditionally format. I.E.
Format cells (x) that are greater than the value in cell (y)
I am not really looking to find cells that are related to any other cells. I just want to format any cell in the column that is greater than a given value. I.E.
Format cells (x) with values of greater than (00:05:00)
All of cells (x) are times with (displayed as hh:mm:ss), and I only want to format values greater than 5 minutes.
Harry Boughen replied to Robert Trowbridge on 09-Jan-15 10:06 PM
Hello Robert,
Select the range that you want to format.

Under your conditional formatting options available is one that says 'Use formula to determine which cells to format'.

Select that option and in the formula bar type = TIMEVALUE(B1) > 0.003472.  Use the first cell in the selected range, I have only used B1 as an example. 

Set your formatting - fill, text colour or whatever and then press Apply and bingo your required cells should be formatted.

Just as an aside, none of the sample values that you gave were under five minutes.

Let me know how you go.

Harry
Robert Trowbridge replied to Harry Boughen on 12-Jan-15 09:22 AM
Harry, thank you very much. Your explanation accomplished exactly what I was trying to do, and now I don't have to run through all 18,000 times individually to find the few that are greater than 5 minutes.