Conditional Formatting issue beginning with CY 2020 dates

[gmaboing]

Hello,

I have a large spreadsheet that I use to find patients that have had a procedure outside of their admission and discharge dates.
The spreadsheet has worked perfectly until 1/1/2020.

The conditional formatting formula I am using is:
Cell Value ---> not between ---> J7 ---> and ---> D7

I am using that formula for cells ---> $BC$7:$BC$1048576

 

[Joe4]

I am assuming that all your dates are actually entered as "Dates" and not "Text" (though that is not clear, as most dates entered are right-justifed and not left-justified, by default).

Do any of your dates have time components that are being hidden (including the entries in J7 and D7)?
You can see these if you change the format of your cells to a format that shows time along with the dates.
Even if you hide these from view by the format you choose, these will still be considered in your formulas and Conditional Formatting, unless you explicitly account for them.

 

[gmaboing]

I'd been bringing them in as text because I need to keep the 10 characters in the date field.

But I guess I need to bring them in as date in order for the conditional formatting to work, then i'll need to reformat them after I've identified the patients. Is that what you're thinking?

 

[gmaboing]

When I bring them in as date, the formatting works. YAY!!!

I'll work on the reformatting part--that shouldn't take too long to fix.

 

[gmaboing]

Thanks for your help Joe ??

 

[Joe4]

You are welcome.

Yes, you cannot do the date comparisons in the Conditonal Formatting if they are text and not dates.
Enter as dates and change the format to your liking, as you have done.