How do I highlight a cell using conditional formatting and dates

[Guest]

I am using Excel 2003.
I want to use a conditional format to identify by a different color when a
date is 90, 180 and 360 days from todays date.

I have two rows of data. The top row contains a room number and the bottom
row contains a date.

m701 m702 m703 m704
12/1/04 7/1/03 10/5/05 12/1/05


I want the top row of data(room number) to change from green, to yellow, to
red as the date in the bottom row varies from todays date by 90,180,360 days.

 

[Max]

One way ..

Assuming the data below is in A12

m701 m702 m703 m704
01-Dec-04 05-May-05 01-Sep-05 01-Nov-05

Select A12 (with A1 active)

Click Format > Conditional Formatting,
make the settings under "Formula Is" as:

Cond1:
=AND(TODAY()-A2>=90,TODAY()-A2<180,A2<>"")
Format light green fill

Cond2:
=AND(TODAY()-A2>=180,TODAY()-A2<360,A2<>"")
Format yellow fill

Cond3:
=AND(TODAY()-A2>360,A2<>"")
Format red fill

Click OK at the main dialog

For the sample data/dates above,
we'd get the fill colours:

m701: red
m702: yellow
m703: light green
m704: default fill (i.e.: no colour)


--

I am using Excel 2003.
I want to use a conditional format to identify by a different color when a
date is 90, 180 and 360 days from todays date.

I have two rows of data. The top row contains a room number and the bottom
row contains a date.

m701 m702 m703 m704
12/1/04 7/1/03 10/5/05 12/1/05


I want the top row of data(room number) to change from green, to yellow, to
red as the date in the bottom row varies from todays date by 90,180,360

days.

 

[Ragdyer]

Select A1 to D1, then:
<Format> <ConditionalFormat>

Click Formula Is, and enter:
=AND(A2>0,A2+360<=TODAY())
Click "Format", and color font RED,
Then <OK>,
Then <Next>, for condition 2,

Click Formula Is, and enter:
=AND(A2>0,A2+180<=TODAY())
Click "Format", and color font Yellow,
Then <OK>,
Then <Next>, for condition 3,

Click Formula Is, and enter:
=AND(A2>0,A2+90<=TODAY())
Click "Format", and color font Green,
Then <OK>, <OK>.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I am using Excel 2003.
I want to use a conditional format to identify by a different color when a
date is 90, 180 and 360 days from todays date.

I have two rows of data. The top row contains a room number and the bottom
row contains a date.

m701 m702 m703 m704
12/1/04 7/1/03 10/5/05 12/1/05


I want the top row of data(room number) to change from green, to yellow, to
red as the date in the bottom row varies from todays date by 90,180,360

days.

 

[Max]

Cond3:

=AND(TODAY()-A2>360,A2<>"")
Format red fill

Slight correction. Formula for Cond3 above should read as:

 

[Guest]

Thanks, this formula worked perfect. Thanks for your help

 

[Max]

Believe my response, albeit slightly different in style,
yields identical results as RD's <g>

 

[Max]

Select A12 (with A1 active)

should have read as: