Conditional Formating Question

[Joneye]

I would like a formula to do the below.

Cell A1 has a date in it, if the date is greater than today it turns green, if less than today it turns red. If no date is in the cell the cell is white.

The formula i have at the moment is;

Red =AND($A1<TODAY())
Green =AND($A1<TODAY())
White ?????

Now because of the way excel works the cell date with nothing in if im correct is 01/01/1900. Hence why im failing.

Any help BRILL

 

[Gerald Higgins]

I think something has corrupted the way your formula appeared in your post.

Did you try to post formulas including the
<
(less than) symbol ?
or
>
(greater than) symbol ?

If yes, try leaving a space after the character. This is a recognised bug on this board.

If there is no date in the cell, what would it contain ?
Would it be blank ? Or text ? Or a number that is not a date ?

 

[Joneye]

= AND ( $ A 1 < TODAY () )

Agreed thats the formula,

 

[Gerald Higgins]

OK thanks. So, what about my final question(s) - what will the cell contain if it doesn't contain a date ?

 

[Joneye]

If the cell is empty it turns red as excel reconise the cell as 01/01/1900. I need this to be ignored if the cell has no date.

 

[Gerald Higgins]

OK. Then maybe something like this

=and(a1> 0,a1< today())

By the way, if you have this formula

=and(a1< today())

it looks as if the AND is not doing anything useful and you can simplify to

=a1< today()

 

[Joneye]

OK now i have learnt something..

THAT WORKS A DAM TREAT.... many thanks