Conditional formatting not working correctly

Question

I want past due dates to turn to a red filled cell. I've tried a variety of formulas but they always turn all the cells red. It's a new spreadsheet for 2020. I copied the information over from last year, cleared all formatting and conditional formatting and created new for this year. Any thoughts on why this isn't working correctly? 

Answer 1

Choose the "Use a formula...." option (the last one on the list shown) with a formula like this, based on the active cell being cell A2

=A2<TODAY()

Answer 2

I've used that formula as well and nothing happens.

Answer 3

You don't pass it the entire range, just the activecell of the range you are selecting.

So, if your are selecting T72:ZZ1000 prior to applying the CF, the formula would be

=T72<TODAY()

Excel will update the formula for all the other cells T73, U72, etc. so that each cell's fill is based on its own value.

DO NOT use $$ in the cell address - those would only be appropriate if you wanted to base a multi-cell range's fill on a single column, row, or cell.

Answer 4

Nope. That didn't work either. I have several conditional formatting things on this 8 worksheet spreadsheet. As employees are hired/termed I adjust the rows and copy the conditional formatting. Last year I didn't have a problem with the one conditional formating I'm trying to do. But I have had to clear it out and redo everything a couple of times because the formatting wasn't working correctly. Could there be something hanging out in the background I'm unaware of? Or is there a limit to the conditional formatting? 

Answer 5

Can you remove any confidential materials and upload a copy to a file sharing service, like onedrive.live.com, and post a link here? I'll take a look - it could be that your dates are actually strings, that there are conflicting older CFs, that there are other values (like N/A as a string), etc. etc.  It's just easier with the file....