Conditional formatting if cell's value is less than today and specific time

Hey guys,

I have a situation I've never encountered before and am having some trouble with it.

I have a column with vlookups in each cell that return a specific date and time from data that I dump into this file each day. I'm trying to set up conditional formatting to highlight the cell and text in red IF the value in the cell, returned by the vlookup, falls before the current day at 9:00:00 am. For example, if the value returned in the cell says Wednesday, April 1, 2015 1:00 PM, I want it to highlight in red. Even if it shows today's date but 8:59 AM or before, I also want it to highlight the same way.

Everything I've been able to come up with so far hasn't done the trick.

Thank you. If you need further clarification, let me know.

Originally Posted by FDibbins

1st, check that your date/time actually IS date/time (numbers) and not text looking like a date/time.
test with =isnumber(cell_ref)

Yes, isnumber is returning a value of True when I use it to verify.

Select the cells/column to which you want to apply conditional formatting.

Conditional formatting -> New Rule -> Use a formula to determine which cells to format

Then enter this formula in the "Format values where this formula is true:" field:

=cell range<(TODAY()+(9/24))

Then you can just assign whichever formatting you want if that formula comes back "True".

Hope this helps.

Thanks for your help. When I enter that, and hit OK, I get the following error in Excel:

"You may not use reference operators (such as unions, intersections, and ranges) or array constants for Conditional Formatting criteria."

See the attached file. I created a reference for today's date at 9AM in cell A2, and then applied the conditional formatting using that cell as a reference.

Here is the formula used in the conditional formatting screen:

=A4<$A$2

You can either select your whole range before entering this formula to make it apply to everything, or use the format painterto apply to your range after you create it for one cell.