Conditional formatting not reading dates

Copper Contributor

Good morning,

 

I have a spreadsheet in which dates have been formatted as dates in the Home tab, and where I already tried using "Text to Columns" in the Data tab to make sure the dates are formatted as dates and not text.  Nevertheless, when I try to apply conditional formatting to highlight dates that are less than TODAY(), it doesn't work as intended - it just highlights everything.   

 

If the data is formatted correctly - as dates - what else could be going wrong?  Or is the data still not formatted correctly?  

5 Replies

@AS2021 

Th rule says Cell Value less than ="TODAY()"

But "TODAY()" is a text string, not a date. Change ="TODAY()" to =TODAY()

@AS2021 Remover the quote marks surrounding =TODAY(). Then it will work.

@AS2021 

 

Used a different formula---Conditional Formatting is great, but not always intuitive, as you're discovering. It almost always takes me a few trial and error steps to get the result I'm seeking. Did here too. 

mathetes_0-1631715177219.png

 

I used "Format only cells that contain". Then applied the "Less than" condition to TODAY(). The quotations were inserted by excel - never would've figured to delete them after having applied the condition: if excel auto-added them, what could be wrong with it? Now I know.
Thank you all.

@AS2021 wrote The quotations were inserted by excel - never would've figured to delete them after having applied the condition: if excel auto-added them, what could be wrong with it?

 

Agreed. One of the deep mysteries of Excel: why auto-insert the quotation marks that render the function inoperable??!!