conditional formats for cells

J

Jason Bartup

I wish for certain cell to be say green if date entered is within one year of
todays date. Then change to red if date is over one year of todays date. I
know this is possible but cant get the right formular
 
D

David Biddulph

Formula is =(ABS($C1-TODAY())<=365) for the green
Formula is =(ABS($C1-TODAY())>365) for the red
But of course it doesn't deal with the subtleties of leap years.
 
E

Epinn

Any chance of someone keying in a date prior to today's date, e.g. yesterday's date? If yes, do you want it green, red or something else?

Epinn

I wish for certain cell to be say green if date entered is within one year of
todays date. Then change to red if date is over one year of todays date. I
know this is possible but cant get the right formular
 
R

Roger Govier

David
But of course it doesn't deal with the subtleties of leap years.
Click to expand...

We could deal with the subtleties of leap year (at least for the next 93
years) with the addition of
+(MOD(YEAR(TODAY()),4)=0) to the 365 in each case.

Your formula with the use of ABS() quite rightly deals with dates that
are within a year of Today (+ or -) but taking Epinn's point where I
think he is suggesting the OP might only want days up to 1 year ahead of
Today, then maybe

=AND($C1>TODAY(),($C1-TODAY())<=365+(MOD(YEAR(TODAY()),4)=0)) for the
green
=AND($C1>TODAY(),($C1-TODAY())>365+(MOD(YEAR(TODAY()),4)=0)) for the
red.

which would leave any cells with dates prior to Today at the default
colouring.
 
Top