2007 conditional formatting not playing ball
 

So I have a spread sheet that has the following formula
=IF(VALUE(G5)<H5,"Breached",VALUE(G5)-H5)

The Colum is formatted to be time as that is what it is calculating. This part is working fine.

What I am then trying to do is to apply the following conditional formatting
Cell Value = Breached - the cell is formatted Pink
Cell Value less than 09:00:00 - The cell is formatted red
Cell Value between 9:00:01 and 15:00:00 - The cell is formatted Orange
Cell Value between 15:00:01 and 30:00:00 - The cell is formatted Yellow
Cell Value greater than 30:00:01 - The cell is formatted Green

They are set up in this order. The Breach formatting works but the rest are not working.

Ie I have a few cells that are displaying 0:25:30 that are green and should be red.

What am I doing wrong?

Have a look at the attached. Does this do what you need?

The attached does do what I need and I replicated that into my spread sheet, but its still not playing ball.

I have attached a copy of what I am trying to get to work with the formula replicated, a lot of them are still coming up as green that should be red etc

It's because you're dealing with date and time in the same cells.

Two ways round this:

1) Split the time and date into separate cells (perhaps not ideal)

2) Add MOD(cell,1) into the conditional formatting formulas.

So for example the formula for red would be =AND(I1<"Breached",MOD(I1,1)<=0.3375) rather than =AND(I1<"Breached",I1<=0.3375)

You would need to do that to all of the last 4 conditional formatting formulas.
Does that work for you?

Thank you Spencer, you are a life saver... It looks like it is now working a treat

Not a problem. Happy to help. :)