Conditional Formatting with AND, ISBLANK, and DATE

Okay, I hate to even post because there are so many conditional formatting questions and answers out there, but everyone has a different way they want to do things, and I can't find a way to make mine work. I'm able to get some things working, but at the same time, it will also be highlighting cells that don't meet the criteria.

Here's what I need.

I have a list columns A-M containing data, C and M are date fields. C will always have a date, and M will only have a date when the row has been "Approved."

If the date in C is greater than 12 days away, I would like the entire row to turn yellow.

If the date in C is less than 12 days away AND the corresponding cell in M is blank, I need the entire row to turn red.

I have managed to get the C cell that is greater than 12 days away to turn yellow by using the "Format only cells that contain" rule with Cell Value - Greater Than - =TODAY()+12

However, it only highlights the cell. I tried using the "Use a formula to determine which cells to format" but couldn't get the syntax right.

I've also messed with the ISBLANK to try to get the row to turn red, and had some close calls, but for some reason it would highlight a few rows that actually had data in them.

Thanks for the help!

Greg (broscup)

Hi Broscup, welcome to the forum.

Try using these options instead. First, select your range of data, e.g. A1:M100. Then click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine..'

Beneath 'Format values where this formula is true', type:

=$C1>TODAY()+12

Click the Format... button and choose the Fill color Yellow. Click OK twice to return to the worksheet.

Click Conditional Formatting > New Rule > Use a formula... a second time, and this time type:

=AND($C1<=TODAY()+12,$M1="")

Click OK twice again, and that should do it. Hope that helps!