conditional formattingbased on dates

N

neowok

seen a few posts about it but cant find one that seems to cover what
need.

i have 4 columns all with dates in format dd/mm/yyyy.

what i want is if the date in a cell is in the next 7 days, colour i
red. if it is between 7 and 14 days from today then colour it yellow
and 14 to 21 days colour it green (and if none then preferably leave i
white if this doesnt complicate it too much).

cant seem to make it work
 
J

jeff

HI,

Go to conditional formatting; select "formula is" then
enter "=A1<=TODAY()+7"; select your color; do "add",
formula is and "=A1<=TODAY()+14", etc.,
=A1<=TODAY()+21. Use the formatting brush to copy
the format across your other cells. (this assumes you
have data in A1, of course)

You may have to adjust the 7, 14,21 to suite your
exact needs.

jeff
 
C

Charlie

Highlight(Paint) the cells containing the dates you want
to format.
In the conditional format screen enter.
'Cell Value is', 'less than or equal to' =TODAY()+7
Pick a color format
Click on add for a second condition.
'Cell Value is', 'between' =TODAY()+8 and =TODAY()+14
Pick a color format
Click on add for a thrid condition
'Cell Valus is', 'between' =TODAY()+15 and =TODAY()+21
Pick a color format

Click enter and you done.

Be sure to use the caps when tying TODAY.

Charlie O'Neill
 
N

neowok

right ive got it working with 1 small problem

the 3 formulars ive got are

first: =O3>=TODAY()+21
second: =O3>=TODAY()+14
third: =O3<=TODAY()+7

the problem is the 3rd one which colours the cell red if the date in i
is within 7 days of today (or in the past). sometimes column O3 i
going to be blank and i dont want it colouring the cell red like it i
doing in the moment. is there a way i can say as part of formula
that it should only do it if O3 is NOT empty?

also while im on the subject, is it possible to have a 4th condition?
it only seems to allow 3, but i may need a 4th at some point.

Thank
 
Top