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(C1TODAY(),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(C1TODAY(),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(C1TODAY(),C1-TODAY()<=30,C1<"")
Cond2: =AND(C1TODAY(),C1-TODAY()<=60,C1<"")
Cond3: =AND(C1TODAY(),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(C1TODAY(),C1-TODAY()<=30,C1<"")
Cond2: =AND(C1TODAY(),C1-TODAY()<=60,C1<"")
Cond3: =AND(C1TODAY(),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($C2TODAY(),$C2-TODAY()<=30,$C2<"")
Cond2: =AND($C2TODAY(),$C2-TODAY()<=60,$C2<"")
Cond3: =AND($C2TODAY(),$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($C2TODAY(),$C2-TODAY()<=30,$C2<"")
Cond2: =AND($C2TODAY(),$C2-TODAY()<=60,$C2<"")
Cond3: =AND($C2TODAY(),$C2-TODAY()<=90,$C2<"")

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