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

"Loc" wrote:

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

"Mike H" wrote:

Hi,

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

and for overdue
=A1<TODAY()

Mike

"Loc" wrote:

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!

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.
--
John C


"Loc" wrote:

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

"Mike H" wrote:

Hi,

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

and for overdue
=A1<TODAY()

Mike

"Loc" wrote:

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,

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" wrote:

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

"Mike H" wrote:

Hi,

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

and for overdue
=A1<TODAY()

Mike

"Loc" wrote:

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!

Thank you, John, for the info.

Loc

"John C" wrote:

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.
--
John C


"Loc" wrote:

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

"Mike H" wrote:

Hi,

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

and for overdue
=A1<TODAY()

Mike

"Loc" wrote:

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!

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

Loc

"Mike H" wrote:

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" wrote:

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

"Mike H" wrote:

Hi,

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

and for overdue
=A1<TODAY()

Mike

"Loc" wrote:

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!