Conditional Formatting Cell Adjacent to Cell Containing Date (past due date tracking)

[chrizzly]

I am working on a spreadsheet to track overdue invoices. I have invoice dates in column B, some cells in column B are blank as they have not been invoiced. Column C includes the dates when the invoices were paid. I am trying to use conditional formatting to highlight cells in column C where the adjacent date in column B exceeds 30 days. If the invoice has been paid and there is a paid date in column C, there should be no highlighting. Both columns B and C are formatted as dates.

I have tried multiple formulas to achieve this:

AND($B2<TODAY()-30,ISBLANK($C3)

IF(ISBLANK($A2),"",DATEDIF($A2,TODAY(),"D")>=30)


I'm getting random cell highlights in column C where dates in column B are empty. I've also noticed instances where dates in column B far exceed 30 days but are not highlighted.

Any input would be helpful.

Chris

 

[chrizzly]

I just noticed the first formula is cut off. Here is the full formula: AND($B2<TODAY()-30,ISBLANK($C3)

 

[chrizzly]

And($b2 < today()-30,isblank($c3)

 

[Fluff]

Try
=And($B2 < today()-30,isblank($C2))
both B2 & C2 should match the first cell in the applies to range

 

[chrizzly]

Yes, the cell reference was an oversight. It doesn't seem to have changed the formatting. I'm still getting the same goofy error. Blank cells in column O still seem to throw the formatting off and result in a highlighted cell in column P. I found that limiting the cell range to only the columns I want helps with identifying dates that are beyond the 30 day mark. Still baffled with the blank cells in column O resulting in highlighting in column P.

 

[Fluff]

What columns have your actual data?
In the OP you were talking cols B & C, now it's O & P.