Need a conditional formatting formula for cells with dates

[joshdog13]

I need a conditional formatting formula that would turn a cell a different color if the date shown in cell A2 were greater than the date in cell A1 by 2 days.

[TMS]

Try:

Please Login or Register  to view this content.

[joshdog13]

Hmmm, that didn't work. It just changed the color in all my cells in the range that are blank that are not returning any data from the formula yet. I attached a sample workbook. The actual column in my sheet is AB where the conditional formatting formula is that I need that I described in this post. I'm getting color in my columns R and T as well in the blanks for those conditional formatting formulas in those ranges. Haven't been able to figure out why. Those conditional formatting formulas in columns T and R do work as intended though when the formulas in the cells return data. Just not sure why the blank are changing color.

[TMS]

Not the most useful sample workbook . No data, no Conditional Formatting, nothing, completely empty

This formula might work better:

Please Login or Register  to view this content.

That said, with the original formula, the CF behaved oddly until I put a formula in to check the CF condition, then it worked properly.

[joshdog13]

Something else must be off. The blank cells turning red went away, but still not getting the cell to turn red if the date is 2 days or more than the other cell's date.

Please see column AB.

[TMS]

You need to check a) the formula and b) competing CF conditions.

This:

Please Login or Register  to view this content.

should probably be this:

Please Login or Register  to view this content.

And you also have a test for M5 = 0 with a grey format. M5 IS zero so AB5 is grey.

For future reference, it's always better if the description of the requirement and the sample file(s)are representative of the live file.

[joshdog13]

So that new formula did change the cell red when the date in H was 2 days before the date in AB, however it turned the entire range red instead of just that cell, including the cells with dates that the condition is not true in.

[TMS]

You only have one row in your sample file …

Maybe you need to change the row number in the formula from absolute to relative. That is, remove the dollar

=AND($H5<>"", $AB5<>"", $AB5>=$H5+2)

[joshdog13]

Yes, removing the extra $ in the formula fixed. I chopped the workbook down to just one sheet and one row to get it under the file size limit. Thank you! I check my conditional formulas in R and T to see if I had a similar issue there but it doesn not seem that is the case. That conditional formula works fine but is turning my blanks cells in the column red with no returned data from formulas. Any idea what is causing that?

[TMS]

Which specific cells in which specific columns are a problem.

Please provide a sample workbook with more, and more specific examples.

You probably need to check ALL your CF conditions. You have a lot that are applied to single cells which could probably be consolidated and some that appear to apply to full columns. That might make your workbook unnecessarily large and affect performance.