conditional formula with date

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!

MikeH
Thu Jul 24 08:17:01 CDT 2008

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

Loc
Thu Jul 24 08:27:00 CDT 2008

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

johnc
Thu Jul 24 08:32:00 CDT 2008

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

MikeH
Thu Jul 24 08:33:00 CDT 2008

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

Loc
Thu Jul 24 08:58:02 CDT 2008

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

Loc
Thu Jul 24 09:00:00 CDT 2008

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