2007 conditional formatting not playing ball

[Mujer]

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 conditiona
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 res
are not working.

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

What am I doing wrong

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

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 conditiona
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 res
are not working.

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

What am I doing wrong?

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

+-------------------------------------------------------------------
|Filename: Mujer Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=490
+-------------------------------------------------------------------

 

[Mujer]

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 sprea
sheet, but its still not playing ball.

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

+-------------------------------------------------------------------
|Filename: Conditional Formatting.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=493
+-------------------------------------------------------------------

 

[Spencer101]

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

I have attached a copy of what I am trying to get to work with th
formula replicated, a lot of them are still coming up as green tha
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 b
*=AND(I1<>"Breached",MOD(I1,1)<=0.3375)* rather tha
*=AND(I1<>"Breached",I1<=0.3375)*

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Mujer]

Thank you Spencer, you are a life saver... It looks like it is no
working a trea

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

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

Not a problem. Happy to help. :

+-------------------------------------------------------------------
+-------------------------------------------------------------------