Background color is not cooperating with my conditional formatting

[mcgeezz]

So I am trying to create a log for run times for some furnaces. What I am trying to show is that the furnaces were not ran past/before a certain amount of minutes, I wanted the background to fill green when the time is between the minimum and maximum time allotment, and turn red when it is not. The conditional formatting works perfectly on the cells without formulas, but when I use
=IF(C16>0,1440*(C16-D16),)
to take the start time (C16), and end time (D16) and 1440 to find minutes, and then set the conditional formatting to turn green if it is between the cells that state 1-10 minutes, and turn red if it is not between 1-10 minutes. the only problem I am facing is that the cells that equal the minimum time are turning red instead of green. To make things worse, this changes frequently, one second it will work and turn green and the next second it will be red. I have set the conditional formatting to stop if the green is true, is there any way to fix this? or create a formula that will do the job of conditional formatting?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]12:01[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]12:10[/TD]
[TD="align: center"]12:30[/TD]
[TD="align: center"]20[/TD]
[/TR]
</tbody>[/TABLE]

 

[bugmonsta]

Hi,

I've copied your sample data into a new sheet - and changed the formula as I started in A2..

I swapped D2 and C2 (your C16 and D16) as your way gives a negative value (maybe a slight typo??)

=IF(C2>0,1440*(D2-C2),)

I then highlighted column E and set two conditional formats up, both using the rule type "Format only cells that contain"

cell value <=10 (format bold green font) - applies to =$E:$E
cell value >=11 (format bold red font) - applies to =$E:$E

and this works for me, I have changed the times and also manually added bigger and negative numbers and the format rules always apply (running in 2016) - so I think your conditional format rules may be erroring for some strange reason. I don't have any rules told to stop if the value is true and this still works.

Try clearing yoru conditional formats and reapply them again to see if it sorts it.

if you have a specific formula running in the conditional format then paste that back for us to try and see if we can track it down.

 

[mcgeezz]

Sorry that was a typo, my bad, and as for my conditional formatting, I need it to be referencing both A1 and B1 or A2 and B2 every time because the allotted times change daily.

I used "Format only cells that contain" and made the formatting to change the background color green if cell value is between =$A$1 and =$B$1, and red if cell value is not between =$A$1 and =$B$1

I tried making it so that it would change font color and even resetting my rules but to no avail.

The rules work however if I put .9 instead of 1 and have the number round up. The only problem is that I cannot do this since I would have to show this spreadsheet to auditors and it cannot state different number then the instructions created to run the furnace. Its very confusing and frustrating so I appreciate any help I can get

 

[bugmonsta]

Ok - I followed your example and I still cannot get it to error on 2016 (it may be a feature in your version??) I set 2 rules like you using the "Format only cells that contain" and colour it if it is between =$A$1 and =$B$1, then run another rule and say colour it if it is not and it works perfectly for me I am afraid.


If I test it with your .9 I get the wrong colour showing as it is not between those figure, however 1.1, 1.9 etc do work as they are.

I cannot think what is causing your CF to not work. As a last resort have you tried creating a new file and putting your times and testing your CF in a brand new file, this way we can eliminate if its your current spreadsheet?

 

[mcgeezz]

I appreciate the help on this, but my boss instructed me to let this go and to not have the colors showing. I was operating on 2016 as well, and thank you for taking the time to help me