How do I highlight a cell using conditional formatting and dates

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.

One way ..

Assuming the data below is in A1:D2

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

Select A1:D2 (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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"shane561" <shane561@discussions.microsoft.com> wrote in message
news:042E305A-A129-444B-9E74-B093ADB50CAC@microsoft.com...
> 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.

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 !
---------------------------------------------------------------------------
"shane561" <shane561@discussions.microsoft.com> wrote in message
news:042E305A-A129-444B-9E74-B093ADB50CAC@microsoft.com...
> 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.

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

Slight correction. Formula for Cond3 above should read as:
> =AND(TODAY()-A2>=360,A2<>"")
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks, this formula worked perfect. Thanks for your help

"Ragdyer" wrote:

> 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 !
> ---------------------------------------------------------------------------
> "shane561" <shane561@discussions.microsoft.com> wrote in message
> news:042E305A-A129-444B-9E74-B093ADB50CAC@microsoft.com...
> > 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.
>
>

Believe my response, albeit slightly different in style,
yields identical results as RD's <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> Select A1:D2 (with A1 active)

should have read as:
> Select A1:D1 (with A1 active)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--