Dates in Conditional Formatting help!!!

[Corey]

I want to set up CONDITIONAL FORMATTING with Dates.
I want to set it so that if the date value in a cell has passed (expired)
the it will be shaded RED,
If the date value is in the next 14days then it wil be shaded yellow,
If the date value in the cel is greater than 14 away yet less than 30 days
then the shade to be green.
If the date value is > than 30 days from today then the shading to remain
white.

How i have set it up is as follows:

sheet1=input sheet (to enter dates)
sheet2= frequecy values in coresponding cells to sheet1(1,2,3,5 years)
sheet3=shaded sheet. This sheet to display the date values that are:

sheet1 input date + sheet2 frequency(yrs)= sheet3 cell value

How do i get the sheet3 dates to be calulated from sheet1 with sheet2 added
to them?

And then to shade them as above??

Regards

Corey

 

[Bob Phillips]

=Date(Year(Sheet1!A2)+Sheet2!A2,Month(Sheet1!A2),Day(Sheet1!A2))

To shade them set the condition to formula is in CF and add conditions, with
a formula of

=A2<TODAY()

=A2<TODAY()+14

=A2<TODAY()+30

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)