conditional formats for cells
 

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

conditional formats for cells
 

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.
--
David Biddulph

"Jason Bartup" <Jason wrote in message
...
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

conditional formats for cells
 

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

"Jason Bartup" <Jason wrote in message ...
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

conditional formats for cells
 

David

But of course it doesn't deal with the subtleties of leap years.

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($C1TODAY(),($C1-TODAY())<=365+(MOD(YEAR(TODAY()),4)=0)) for the
green
=AND($C1TODAY(),($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.
--
Regards

Roger Govier


"David Biddulph" wrote in message
...
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.
--
David Biddulph

"Jason Bartup" <Jason wrote in
message ...
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