Conditional Formatting and an IF statement

[honeybee129]

I need to know if it's possible to make Excel use an IF statement to decide whether to apply conditional formatting to a cell. What I have is this:

If cell G2 is greater than or equal to .15 then cell D2 should have no conditional formatting applied to it, cell should have no fill.

If cell G2 is less than .15 then the following conditional formatting should be applied:
Condition 1: If cell D2 value is greater than or equal to 00:06:00 cell fill color should be green
Condition 2: If cell D2 value is less than 00:06:00 cell fill color should be red

 

[Scott Huish]

What you are asking is possible, but you don't need an IF statement to do it, you just need 2 conditional formatting formulas:

Condition 1, Formula Is:
=AND($G2>=0.15,$D2>="06:00"+0)
Format with green fill

Condition 2, Formula Is:
=$G2>=0.15
Format with red fill.

 

[honeybee129]

Thank you, I hadn't thought of using a formula. We ended up making it work by using 3 different conditions:

=G2>=0.15
Formatting: none

=(D2>$D$22)
formatting: green fill

=(D2<$D$22)
Formatting: red fill

D22 has the goal of 00:06:00 listed in it, for some reason we couldn't get the formula in the conditional fomatting to work with a time in it, but it worked with a cell reference to that amount.

 

[Jonmo1]

=(D2<$D$22)
Formatting: red fill

D22 has the goal of 00:06:00 listed in it, for some reason we couldn't get the formula in the conditional fomatting to work with a time in it, but it worked with a cell reference to that amount.

To make it work with the time in the formula, try like this

=(D2>"00:06:00"+0)

or

=(D2>TIME(0,6,0))

 

[honeybee129]

Making this conditional formatting made me think of something I'd like to try in a different file, it's similar to what we did in this one but just different enough that I need a little help with the formula. What I'd like to do is put conditional formatting on cell C2 that looks to see if cell D2 is blank or not. If cell D2 is blank, cell C2 should have no formatting. If cell D2 is nonblank and cell C2 is blank, cell C2 should be filled yellow. If both cells are nonblank cell C2 should have no formatting. The information in cell D2 will be an eight digit number, so I'm pretty sure my first condition can be something like:

Condition 1 Formula is:
=D2>1
formatting: none

What I need help with is the formula for the second and third conditions, would I use an IF statement, Counta statement, or an And statement?

 

[Scott Huish]

You don't need conditions for no formatting, you only need conditions for what you want to format. Conditional Formatting only applies for something that can be evaluated as TRUE.

Your formula could be:

=AND($C2="",$D2<>"")

 

[Kaml005]

Thank you, I hadn't thought of using a formula. We ended up making it work by using 3 different conditions:

=G2>=0.15
Formatting: none

=(D2>$D$22)
formatting: green fill

=(D2<$D$22)
Formatting: red fill

D22 has the goal of 00:06:00 listed in it, for some reason we couldn't get the formula in the conditional fomatting to work with a time in it, but it worked with a cell reference to that amount.

The reason why reference method is working: when you use reference in a formula, excel calulates the numerical value of referred cell contents . So if the reference contain time format, excel in background converts it to value.

Say for exapmle
=value(06:00:29) is 0.250336
and =
value(06:00:30) is 0.250347.

Obviously 0.25347 is > 0.250336. So your formula comes True/False and CF aplies accordingly. If you write time content directly without using "+0" it should not work because of format compatibility issues in formula execution.

 

[npratt]

I would like to create a conditional formatting rule for the following:

Cell C2: If cell C1=36 then the text in C2 would be 3x/8 weeks, 2x/6 weeks (36)

Once that is completed, I'd like to create additional conditional formatting rules for the same cell.

Is that possible?

 

[snekkis]

<today());true;false))
<today());true;false))
<today());true;false))[ quote]
IF(AND(AE2="YES",E2>TODAY()),1,IF(AND(AE2="YES";F2<today()),1,0))

Trying this, but it does not work in conditional formatting, obviously, but this is what I'm trying to do.

When AE2 is Yes and the date in E2 is greater than todays, the cell should be red.

When AE2 is Yes and the date in F2 is smaller than todays, the cell should also be red.

I have edited several times now, trying to show the whole
IF statement, but it is cut short in the post</today()),1,0))
</today());true;false))[></today());true;false))
</today());true;false))

 

[Scott Huish]

Drop the IF, you need something that generates a TRUE/FALSE value:

Try this:
=AND(AE2="Yes",OR(E2>TODAY(),F2<TODAY()))