Conditional Formating - using time HH:MM:SS AM/PM

**OldTownComputers** · 03-01-2013, 03:06 PM

Hello,
New member here hoping this will be my salvation. I've searched for HOURS trying to figure this darn thing out. It seems as if every other conditional formatting formula is easy as pie except when it comes to Time values!!! Or maybe it's just me? :-P

Anyways, every month I have column D that is populated with time values in the HH:MM:SS AM/PM format. What I would like is just a simple time condition that changes the font color to red if the value is after 5:00pm in any of the rows in Column D. Seems simple enough and I've tried at least a dozen attempts so far but none work properly. Some highlight ever row in the column, some highlight random rows.

I'm missing something here and greatly appreciate the help.

Luke

**tigeravatar** · 03-01-2013, 03:12 PM

Luke,

Welcome to the forum!
Give this a try as your conditional format formula:

Please Login or Register  to view this content.

**Pete_UK** · 03-01-2013, 03:14 PM

First, select all your data - assume this is from A2 to K100 (doesn't matter what it is - that just gives me something to refer to). Then bring up the Conditional Formatting dialogue box (New Rule) and choose Use a Formula ..., and then enter this formula in the box:

=$D2>17/24

(i.e. 17:00 in the 24-hour clock), then click on the Format button, then the Color drop-down and choose Red. OK your way out, then Excel will automatically adjust those cell references to apply to the whole block.

Hope this helps.

Pete

**OldTownComputers** · 03-01-2013, 04:10 PM

drats, sorry guys neither worked. In this particular batch i'm working in theres only 1 row it looks like that should be highlighted(its cell value is 8:00:00 PM)

Tiger - your formula highlighted 2rows, one of which was 12:00:00PM and one was 4:45:00PM
Pete - your formula highlighted every row in the column

Shrug?? haha I really appreciate the replies though! What can I double check in my sheet.

Luke

**tigeravatar** · 03-01-2013, 04:17 PM

Luke,

I think at this point we'd need to see a sample workbook for why you're having issues. I mocked up a quick sample workbook and was unable to duplicate your described problem.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

**Pete_UK** · 03-01-2013, 04:21 PM

I suspect your times are not in Excel format but are text values that happen to look like time values.

Pete

**OldTownComputers** · 03-01-2013, 06:54 PM

ExcelExport.xls
Hrm how strange. I went back into the formatting for the cells of column D and applied the time format(after applying the format I did not detect any actual changes to the cell data) and it appears to be working.
However I opened another month's excel sheet, applied the time format to the column, then applied the CF and it's highlighting incorrect values.

Pete I bet you are correct. These excel sheets actually start as an export from my Outlook calendar and it's the times of those export items I'm working with. Despite having Office 2010, the calendar exports only export as xls format. Not sure if I'm losing much with that.

I'm attaching an export sheet at the moment.

**tigeravatar** · 03-01-2013, 07:45 PM

Luke,

After looking at your posted workbook, Pete has it right on the money, you have many entries in columns B, C, and D that are text instead of using Excel datecodes. To correct this, in cell K2 put this formula:

Please Login or Register  to view this content.

Copy over to L2 and then copy down until you have no more rows with data. Copy the block of decimal numbers, select cell C2, right-click, paste-special -> values. Then format the cells as custom to hh:mm:ss AM/PM
Afterwards you can delete what is in columns K:L
Then apply the conditional formatting again.