Conditional formatting using two criteria - is this possible

Question

Dear All,

I am trying to apply conditional formatting to two seperate columns but I cannot work out how (or even if I can) do this.  I am tracking temperature alarms in a warehouse and would like to change the colour of the cell when certain criteria are met.  My datasheet has columns for:

total time/temp/action.  So for example if the temperature is below 15oC for 8hrs I would like the action cell to turn red, prompting me to generate a report on the issue.  I feel that this should be pretty easy to do but I'm having a really hard time with it.

Thanks

Stef

Answer 1

Let's say your data are in A2:C100 (with headers in row 1).

Select C2:C100. I'll assume that C2 is the active cell within the selection.

Click Conditional Formatting > New Rule... on the Home tab of the ribbon.

Select "Use a formula to determine which cells to format.

Enter the formula =AND(A2>TIME(8,0,0),B2<15)

Click Format...

Specify the desired formatting.

Click OK twice.

I have assumed that column A contains times such as 01:30. If it contains number, e.g. 1.5, change the formula to =AND(A2>8,B2<15)

Answer 2

Make sure that C2 is the active cell within the selection when you set the conditional formatting rule.

Answer 3

This formula is throwing up some weird results. Typed as above it highlights some cells which fit the criteria and some that don't and misses some that do. For example (column 1=time, column 2=temp):

1:05:00	14.9
0:02:00	15
0:45:00	9.9

• Row 1 fits the criteria but is not orange,
• row 2 does not fit the criteria but is orange,
• row 3 does not fit the criteria and is not orange.

The only thing I have altered from the formula is the cell (my data is in F and G not A and B).  My temperature is numbers only not units (i.e. 15 not 15oC).

Thanks

Stef

Answer 4

You can use the formula

=AND(A2>TIME(1,0,0),B2>0,B2<15)

If you want to include 0° en 15°, use >= and <= instead of > and <.

Answer 5

Thanks Hans,

That has worked perfectly.  Anyone else following the instructions just note you need to exclude your header row when you select the cells to format or it goes wrong (I did this first time).

Could I adjust the formula to detect a range, e.g. if the temperature is between 0-15oc for more than one hour?  I have had a go at it myself but without any luck.

Stef