Conditional formatting not reading dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2752792%22%20slang%3D%22en-US%22%3EConditional%20formatting%20not%20reading%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752792%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20in%20which%20dates%20have%20been%20formatted%20as%20dates%20in%20the%20Home%20tab%2C%20and%20where%20I%20already%20tried%20using%20%22Text%20to%20Columns%22%20in%20the%20Data%20tab%20to%20make%20sure%20the%20dates%20are%20formatted%20as%20dates%20and%20not%20text.%26nbsp%3B%20Nevertheless%2C%20when%20I%20try%20to%20apply%20conditional%20formatting%20to%20highlight%20dates%20that%20are%20less%20than%20TODAY()%2C%20it%20doesn't%20work%20as%20intended%20-%20it%20just%20highlights%20everything.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20data%20is%20formatted%20correctly%20-%20as%20dates%20-%20what%20else%20could%20be%20going%20wrong%3F%26nbsp%3B%20Or%20is%20the%20data%20still%20not%20formatted%20correctly%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2752792%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752889%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20reading%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F987078%22%20target%3D%22_blank%22%3E%40AS2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETh%20rule%20says%20Cell%20Value%20less%20than%20%3D%22TODAY()%22%3C%2FP%3E%0A%3CP%3EBut%20%22TODAY()%22%20is%20a%20text%20string%2C%20not%20a%20date.%20Change%20%3D%22TODAY()%22%20to%20%3DTODAY()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752904%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20reading%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752904%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F987078%22%20target%3D%22_blank%22%3E%40AS2021%3C%2FA%3E%26nbsp%3BRemover%20the%20quote%20marks%20surrounding%20%3DTODAY().%20Then%20it%20will%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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??!!