Conditional Formatting question

**Avenger291** · 04-03-2013, 11:45 AM

Hello,

I have a question concerning conditional formatting.

I have a column(Z) of dates in a spreadsheet.
I am using this formula to color dates that meet a fixed criteria green:

Formula:

Please Login or Register  to view this content.

the issue I am having is that it works great for all cells except 1.
This cell has the same date as another cell in the column.
One cell turns green and one does not.

I cannot see any reason it does not work.

Any ideas greatly appreciated.

**etaf** · 04-03-2013, 01:02 PM

can we see the spreadsheet

**Special-K** · 04-03-2013, 01:07 PM

One of them is probably NOT a date

Use ISTEXT to check the offending cell, if it's TRUE then its not a date, (probably spaces in there somewhere)

**Avenger291** · 04-03-2013, 03:02 PM

thanks for the feedback

I couldn't upload the spreadsheet due to PII restrictions and FOUO government information.

I did check to see if all the dates were formatted as DATE and they were.
I also checked reference cell F2 to be sure it was formatted as TEXT and they were.

I did however fix the problem, though not sure why it worked.

I deleted contents of the cell that would not change color to green re-entered date still not working.
So I deleted contents again and re-entered date again then manually colored it green.
Next I deleted contents again cell turned white then re-entered date and voila! the cell changed green!

now it is working as expected for all date ranges.
Not really sure what was wrong.

Thanks again for the inputs