Merging two formulas for use in conditional formatting

[maximillianrg]

Thank you for your help in advance

I'm trying to figure out how apply conditional formatting to a date in cell J49 bases on two factors, the date in cell J49 and the contents of cell A49, here is the logic. In the below examples, bad means conditional formatting would be applied (the date would be changed to RED), and good means conditional formatting would not be applied (the date would be black or default)

• =IF(AND(A49=0,J49<>TODAY()),"bad","good") so if today is 2/6 and the date in cell J49 is not 2/6 AND cell A49 = 0 conditional formatting would be applied
• =IF(AND(A49="",TODAY()-J49>2,"bad","good") so if today is 2/6 and the date in cell J49 was 2/4 or earlier conditional formatting would be applied

So what I'm trying to do is combine and modify the two formulas into one so that conditional formatting would be applied to the date in cell J49 based on those formulas

Thoughts?

 

[DanteAmor]

Try this

=AND(TODAY()-J49>=2,OR(A49="",A49=0),J49<>"")

 

[Joe4]

Just use an OR condition to check for either of the combinations that return bad, i.e.
=OR(AND(A49=0,J49<>TODAY()),AND(A49="",TODAY()-J49>2))

 

[maximillianrg]

Hello Joe and Dante - Thank you for your comments. I tried both formulas by doing the following and both produced the same results, cell J49 was highlighted green regardless of the date in cell J9 or the cell of value in cell A49

1. Select Cell J49, select conditional formatting, select "use a formula to select which cells to format
2. Copy formula into formula field, click format button, select fill color green
3. Press OK on Fill color box and OK on new formula rule

 

[Joe4]

Please give us an actual example that is not working for you - please provide concrete examples of values in cells A49 and J49 (note that J9 was not part of the question).

 

[Joe4]

Note that my original formula should do the EXACT same thing as your original formulas did. So, if there is an issue my formula, there is probably as issue with your formula (it probably isn't working like you thought it was).

As matter as fact, I think I see a problem with it.

=IF(AND(A49="",TODAY()-J49>2,"bad","good") so if today is 2/6 and the date in cell J49 was 2/4 or earlier conditional formatting would be applied

That is a false statement. The difference between 2/6 and 2/4 is EXCACTLY 2 days. So your formula will NOT return "bad" unless you change it to >=2

Since I based my formula off of your original formula (assuming it was correct), we would need to change also to:
=OR(AND(A49=0,J49<>TODAY()),AND(A49="",TODAY()-J49>=2))

 

[DanteAmor]

cell J49 was highlighted green regardless of the date in cell J9 or the cell of value in cell A49

Here I show you the possibilities according to your rules.

If that is not what you need, you could give examples.
Forget your formulas a bit and explain with examples in which cases you want to paint highlight the cell.

 

[maximillianrg]

Gentlemen - I truly want to thank you for your help. The below image captures the conditional formatting affects I am trying to achieve.

 

[DanteAmor]

Try this:

=AND(TODAY()-J49>=0,TODAY()-J49<=2,OR(A49="",A49=0))

 

[maximillianrg]

Dante - this is so close, it worked in every instance except row 2