conditional formula with date

  • Thread starter Thread starter Loc
  • Start date Start date
L

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!
 
Hi,

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

and for overdue
=A1<TODAY()

Mike
 
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
 
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.
 
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
 
Thank you, John, for the info.

Loc

John C said:
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.
Click to expand...
 
Back
Top