Conditional formatting with time

Dear all,

I'm trying to apply a conditional formatting to a column and I would like to highlight all time values >= 9:30:00

My current format does this, but unfortunately it also formats the blank ("") cells.

I guess I need some IF condition or NOT to resolve this, but I'm too inexperienced to find the solution (and Google doesn't help ).

Solution

IF blank ("") THEN no formatting
IF < 9:30:00 THEN no formatting
IF >= 9:30:00 THEN formatting

Could any1 help?

Best regards,

dunnobe

Hi dunnobe- Select "Use a formula to determine which..." and apply this CF formula in E2:
=N(E2)>=TIME(9,30,0)

Hi leelnich,

It works, but what is =N and do I have to put dollar signs?

How do I pick the traditional red "bad" color with such a conditional formatting?

1) The N() function coerces a value into a number. In this case, "" is changed to zero so Excel can compare it correctly.
2) No. Dollar signs dictate ABSOLUTE (fixed) vs. RELATIVE addressing. In this case, you need relative addressing so the formula adjusts in each cell.
3) In the Conditional Formatting Dialog box, click Edit..., click Format..., then select the Fill tab. Now select from the color palette or click More Colors....

Excel's N() function
Excel Addresses

*Never mind. I managed to copy-paste most of the Bad-style's properties.

Thank you for your help.

dunnobe