color code a row by expiration date?

I am creating a spreadsheet that lists all of our billboard inventory with
one column containing the date the billboard becomes available (example: if
the contract expires 9/30/06 then the "available date" is 10/1/06). I want to
know if there is a way to create a function that when the "available date"
comes within 30 days before todays date it could either change colors or do
something to mark it is going to expire w/in the 30 days?

Assuming the available dates are listed down in col C
Select col C (C1 active)
Click Format > Conditional Formatting
Under Condition 1, make it as:
Formula is:
=AND(C1>TODAY(),C1-TODAY()<=30,C1<>"")
Click to Format to taste, OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kate" wrote:
> I am creating a spreadsheet that lists all of our billboard inventory with
> one column containing the date the billboard becomes available (example: if
> the contract expires 9/30/06 then the "available date" is 10/1/06). I want to
> know if there is a way to create a function that when the "available date"
> comes within 30 days before todays date it could either change colors or do
> something to mark it is going to expire w/in the 30 days?

thank you.. that did help.. okay.. i have another question... is there a way
to make 30 days one color 60 days another color and 90 days another color?
you are such a big help!!!!!

"Max" wrote:

> Assuming the available dates are listed down in col C
> Select col C (C1 active)
> Click Format > Conditional Formatting
> Under Condition 1, make it as:
> Formula is:
> =AND(C1>TODAY(),C1-TODAY()<=30,C1<>"")
> Click to Format to taste, OK out
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Kate" wrote:
> > I am creating a spreadsheet that lists all of our billboard inventory with
> > one column containing the date the billboard becomes available (example: if
> > the contract expires 9/30/06 then the "available date" is 10/1/06). I want to
> > know if there is a way to create a function that when the "available date"
> > comes within 30 days before todays date it could either change colors or do
> > something to mark it is going to expire w/in the 30 days?

Apply the 3 cond format formulas below in sequence:
Cond1: =AND(C1>TODAY(),C1-TODAY()<=30,C1<>"")
Cond2: =AND(C1>TODAY(),C1-TODAY()<=60,C1<>"")
Cond3: =AND(C1>TODAY(),C1-TODAY()<=90,C1<>"")
Format the trigger fill colours for Conds 1 to 3 as desired
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kate" wrote:
> thank you.. that did help.. okay.. i have another question... is there a way
> to make 30 days one color 60 days another color and 90 days another color?
> you are such a big help!!!!!

OKAY..last question... is there a way to make the function recognize the date
but make the whole row the color i format it to be instead of just the cell?

"Max" wrote:

> Apply the 3 cond format formulas below in sequence:
> Cond1: =AND(C1>TODAY(),C1-TODAY()<=30,C1<>"")
> Cond2: =AND(C1>TODAY(),C1-TODAY()<=60,C1<>"")
> Cond3: =AND(C1>TODAY(),C1-TODAY()<=90,C1<>"")
> Format the trigger fill colours for Conds 1 to 3 as desired
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Kate" wrote:
> > thank you.. that did help.. okay.. i have another question... is there a way
> > to make 30 days one color 60 days another color and 90 days another color?
> > you are such a big help!!!!!

"Kate" wrote:
> OKAY..last question... is there a way to make the function recognize the date
> but make the whole row the color i format it to be instead of just the cell?

We just need to fix it (using the dollar sign) to the col C,
ie essentially tweak the 3 earlier cond format formulas below to:
Cond1: =AND($C2>TODAY(),$C2-TODAY()<=30,$C2<>"")
Cond2: =AND($C2>TODAY(),$C2-TODAY()<=60,$C2<>"")
Cond3: =AND($C2>TODAY(),$C2-TODAY()<=90,$C2<>"")
and then format the trigger fill colours for Conds 1 to 3 as desired

Here's a quick way to go about applying it say, to cols A to E, assuming
we've set it up earlier in col C (wo the dollar signs).

Select col C again, then Format > Cond Format to bring up the dialog
Put in the dollar signs as above for the 3 conditions, OK out
Select cols A to E, click Format > Cond Formatting, click OK, and that's it
(If we wanted the CF applied to the entire row (ie all cols), we'd just
select the entire sheet instead of selecting cols A to E)

If we're setting it up from scratch, then we'd select cols A to E first (A1
active) and then apply the 3 cond format formulas given above.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Typo correction, sorry. It should be C1 instead of C2.

Lines:
> Cond1: =AND($C2>TODAY(),$C2-TODAY()<=30,$C2<>"")
> Cond2: =AND($C2>TODAY(),$C2-TODAY()<=60,$C2<>"")
> Cond3: =AND($C2>TODAY(),$C2-TODAY()<=90,$C2<>"")

should have read as:
Cond1: =AND($C1>TODAY(),$C1-TODAY()<=30,$C1<>"")
Cond2: =AND($C1>TODAY(),$C1-TODAY()<=60,$C1<>"")
Cond3: =AND($C1>TODAY(),$C1-TODAY()<=90,$C1<>"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---