Conditional Format question

[austenr]

I am trying to do conditional formatting on a column whose contents are dates formatted as follows: xx/xx/xx. I amtrying to highlight every cell that is GT 01/01/2005. When I apply the formatting it highlights cells that contain for example 01/01/2004. Why is that? Any help would be appreciated.

[Zack Barresse]

Hi Austen,

Some questions:

What is the exact value you have in a cell?
What is the exact conditional format? (FormulaIs?)
What is the exact range of cells to be formatted?
Are there any special formats used on these cells?

Can you maybe post a spreadsheet sample?

[austenr]

Picture the column as having 12/31/2004 or a date like that in every cell. There is no special formatting on any of the cells. It acts like it is evaluating each set of numbers, ex.12 then 31 then 2004. Is there a way to look at the cell as a whole?

[austenr]

Oh sorry the range is from a2 through A25000.

[Zack Barresse]

What is your current Conditional Format? What do you want it to be? Is "GT" Greater Than? So anything greater than the date of January 1, 2005? If this is the case ...

Select A2:A25000, ensure you have A2 as the activecell (the lighter highlight color).
Format --> Conditional Formatting.
Formula Is...
=A2>38353

Make sure that A2 does not have any absolute referencing in it.

[Ken Wright]

Just for reference you might find it easier to see what it is that is going on in the CF if you use one of the DATE functions, eg


=A2>DATEVALUE("01/01/2005")

That way you don't have to convert the number in the formula to see what date you are using and vice versa.

Regards
Ken...............