How to use conditional formatting for dates based on two cells?

Question

I have a due date column (lets say column B) and a paid column (column C). I want the due date column to be highlighted Green or Red if the date is this week (red) or next week (green), but I only want the highlight to show if the Paid column (c) is blank- meaning it hasn't been paid yet. I was able to use conditional formatting to highlight the due dates, but I can't figure out how to edit the formula so that it will only show if the paid column is blank. Can someone tell me how to edit this?

Answer 1

From your screen shot, can't see the column that should be referenced in your conditional format

=NOT(ISBLANK....   

I assume you have AT1 in there ?

Answer 2

I must be doing something wrong cause its still not working... Here is a snip

See how cell AS18 is still red even though cell AT18 has a number? I want it to not be red since that bill has been paid.

Answer 3

In that case just select all your cells in column B & add a new conditional format based on a formula

=NOT(ISBLANK(C2))

Make it no colour fill as the format, make sure it is the top of your conditional format list and then tick the box Stop if True.

Answer 4

I don't know the formula- I literally did select cells, conditional formatting, highlight cell rules, a date occurring this week with light red fill with dark red text and new rule- a date occurring next week with light green fill with dark green text.

So how do I find out the formula?

Answer 5

You haven't posted your Green & Red conditional format formulae but you should just be able to add to it using the AND function

=AND(... existing formula ... , ISBLANK(C2))