Conditional formatting in a worksheet

Hi,

I have a list of items and each row has a cell with an exipiration date. (I
have no idea how hard or easy this is) I would like to format the date cell
so that if the date entered will be coming up in 3 months or less, the cell
gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06,
Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see
that the expiration date is less than 3 months away.

Can anyone help?

Hi Andy,

try on the conditional format

the cell value is | between | =a1 | and | =a1+90

hth
regards from Brazil
Marcelo



"Andy Shapiro" escreveu:

> Hi,
>
> I have a list of items and each row has a cell with an exipiration date. (I
> have no idea how hard or easy this is) I would like to format the date cell
> so that if the date entered will be coming up in 3 months or less, the cell
> gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06,
> Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see
> that the expiration date is less than 3 months away.
>
> Can anyone help?

Use a formula of

=AND(B2>=TODAY(),B2<=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))

and copy the format across

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Andy Shapiro" <Andy Shapiro@discussions.microsoft.com> wrote in message
news:73E5A79F-4B83-4DA6-AC29-469AC6771017@microsoft.com...
> Hi,
>
> I have a list of items and each row has a cell with an exipiration date.
(I
> have no idea how hard or easy this is) I would like to format the date
cell
> so that if the date entered will be coming up in 3 months or less, the
cell
> gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06,
> Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see
> that the expiration date is less than 3 months away.
>
> Can anyone help?

Select the cell with the date in it
Go to Conditional formatting
Use
Cell Value - Less than - =today()+90
and format with a colour ( I would select red)
Set up a second condition
Cell Value - Equals - =today()+90
and format with a colour (say blue)
Set up a third condition
Cell value - greater than - =today()-90
and format with a colour ( say green)
Now with this cell selected use format painter and apply to all the cells
where this test is required.
You will then find that all dates less than 90 day will be highlighted in
red, exactly 90 days will be blue and greater than 90 days will be green
Hopefully this is exactly what you want
Cheers
--
Don C


"Andy Shapiro" wrote:

> Hi,
>
> I have a list of items and each row has a cell with an exipiration date. (I
> have no idea how hard or easy this is) I would like to format the date cell
> so that if the date entered will be coming up in 3 months or less, the cell
> gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06,
> Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see
> that the expiration date is less than 3 months away.
>
> Can anyone help?

Thank you to all! I'll try them out right now!

"DonCam65" wrote:

> Select the cell with the date in it
> Go to Conditional formatting
> Use
> Cell Value - Less than - =today()+90
> and format with a colour ( I would select red)
> Set up a second condition
> Cell Value - Equals - =today()+90
> and format with a colour (say blue)
> Set up a third condition
> Cell value - greater than - =today()-90
> and format with a colour ( say green)
> Now with this cell selected use format painter and apply to all the cells
> where this test is required.
> You will then find that all dates less than 90 day will be highlighted in
> red, exactly 90 days will be blue and greater than 90 days will be green
> Hopefully this is exactly what you want
> Cheers
> --
> Don C
>
>
> "Andy Shapiro" wrote:
>
> > Hi,
> >
> > I have a list of items and each row has a cell with an exipiration date. (I
> > have no idea how hard or easy this is) I would like to format the date cell
> > so that if the date entered will be coming up in 3 months or less, the cell
> > gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06,
> > Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see
> > that the expiration date is less than 3 months away.
> >
> > Can anyone help?

Don,

I loved your idea. Absolutely PERFECT! I changed the formatting slightly to
Red, Yellow, Green.

Again, Thanks to al!

"DonCam65" wrote:

> Select the cell with the date in it
> Go to Conditional formatting
> Use
> Cell Value - Less than - =today()+90
> and format with a colour ( I would select red)
> Set up a second condition
> Cell Value - Equals - =today()+90
> and format with a colour (say blue)
> Set up a third condition
> Cell value - greater than - =today()-90
> and format with a colour ( say green)
> Now with this cell selected use format painter and apply to all the cells
> where this test is required.
> You will then find that all dates less than 90 day will be highlighted in
> red, exactly 90 days will be blue and greater than 90 days will be green
> Hopefully this is exactly what you want
> Cheers
> --
> Don C
>
>
> "Andy Shapiro" wrote:
>
> > Hi,
> >
> > I have a list of items and each row has a cell with an exipiration date. (I
> > have no idea how hard or easy this is) I would like to format the date cell
> > so that if the date entered will be coming up in 3 months or less, the cell
> > gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06,
> > Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see
> > that the expiration date is less than 3 months away.
> >
> > Can anyone help?

Thank you Andy
Pleased I could help
--
Don C


"Andy Shapiro" wrote:

> Don,
>
> I loved your idea. Absolutely PERFECT! I changed the formatting slightly to
> Red, Yellow, Green.
>
> Again, Thanks to al!
>