Sep 15 2021 06:40 AM
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?
Sep 15 2021 07:06 AM
Th rule says Cell Value less than ="TODAY()"
But "TODAY()" is a text string, not a date. Change ="TODAY()" to =TODAY()
Sep 15 2021 07:08 AM
@AS2021 Remover the quote marks surrounding =TODAY(). Then it will work.
Sep 15 2021 07:13 AM
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.
Sep 16 2021 08:18 AM
Sep 16 2021 08:24 AM
@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??!!