Conditional formatting in a worksheet

A

Andy Shapiro

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?
 
M

Marcelo

Hi Andy,

try on the conditional format

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

hth
regards from Brazil
Marcelo



"Andy Shapiro" escreveu:
 
B

Bob Phillips

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)
 
D

DonCam65

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
 
A

Andy Shapiro

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

DonCam65 said:
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
Click to expand...
 
A

Andy Shapiro

Don,

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

Again, Thanks to al!
 
Top