color code a row by expiration date?

[Guest]

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?

 

[Guest]

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

 

[Guest]

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!!!!!

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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.

---

 

[Guest]

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

---