Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Quote:
Last edited by Spencer101 : July 24th 12 at 08:36 AM |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
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? |
#5
|
|||
|
|||
Thank you Spencer, you are a life saver... It looks like it is now working a treat
|
#6
|
|||
|
|||
Not a problem. Happy to help. :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2002 excel isn't playing well with 2007 | Excel Programming | |||
Conditional Formatting 2007 | Excel Worksheet Functions | |||
Detecting the actual playing or NOT playing of a WAV file | Excel Discussion (Misc queries) | |||
Conditional formatting not playing ball | Excel Discussion (Misc queries) | |||
Conditional formatting not playing ball (2) | Excel Discussion (Misc queries) |