Conditional formatting not reading dates

Question

Good morning,&nbsp;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.&nbsp; 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.&nbsp; &nbsp;&nbsp;If the data is formatted correctly - as dates - what else could be going wrong?&nbsp; Or is the data still not formatted correctly?&nbsp;&nbsp;

as2021 · Answer

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.

hansvogelaar · Answer

AS2021&nbsp;
Th rule says Cell Value less than ="TODAY()"
But "TODAY()" is a text string, not a date. Change ="TODAY()" to =TODAY()

riny_van_eekelen · Answer

AS2021&nbsp;Remover the quote marks surrounding =TODAY(). Then it will work.

mathetes · Answer

AS2021&nbsp;&nbsp;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.&nbsp;&nbsp;

mathetes · Answer

AS2021&nbsp;wrote&nbsp;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?&nbsp;Agreed. One of the deep mysteries of Excel: why auto-insert the quotation marks that render the function inoperable??!!

Forum Discussion

Conditional formatting not reading dates

6 Replies

Resources