conditional formula with date

[Loc]

I have a delivery date column with mmm-yy format. How can I write a
conditional formula so that the date turns yellow when it's due in 30 days
and turns red when it's over due? Please help!

 

[Mike H]

Hi,

For closer than 30 try
=AND(A1<TODAY()+30,A1>=TODAY())

and for overdue
=A1<TODAY()

Mike

 

[Loc]

Mike,
The formula you wrote is for one cell. If I want to apply this formula to
whole column, should I replace A1 with A1:A5000? Thank you for the help.

Loc

 

[John C]

What Mike is saying, after you do the conditional format for the 1 cell, then
highlight the entire selection, go to Conditional Formatting, and press OK.
So long as your cell refernce is not anchored (with $'s), then it will
progress accordingly for all your range.

 

[Mike H]

Hi,

From your original post I thought you understood how to enter it for
multiple cells.

If the cells are in column A select the range of cells and then
Format|Conditional Format
Formula is
enter the formula
pick a colour
OK

You will then find excel has been smart enough to increment A1 to the
appropriate address for each cell you had selected. If it's a column other
than A then change the A to the appropriate column. If the range started in
D6 for example you would select D6 down and change A1 to D6 and enter the
formula.

Mike

 

[Loc]

Thank you, John, for the info.

Loc

What Mike is saying, after you do the conditional format for the 1 cell, then
highlight the entire selection, go to Conditional Formatting, and press OK.
So long as your cell refernce is not anchored (with $'s), then it will
progress accordingly for all your range.

 

[Loc]

Thank you, Mike. It works like advertised. Thanks again.

Loc