conditional formatting dates

P

Phil

I maintain a "DEMO" spreadsheet that has a return date for the product of
mm/dd/yyyy. I would like to have that highlighted 7 days before the product
is due back. Any help with a formula would be greatly appreciated!! Also, is
there an ability to format so it also emails me a notice?

Thanks,

Phil Hamm
 
C

CyberTaz

Hi Phil-

One Option, there are others- In Format>Conditional Formatting:

Cell Value is Less than or equal to =NOW()-7

Set your cell format specs to how you want the date to display.

HTH |:>)
 
B

Bob Phillips

Use a formula of

=AND(A1<>"",A1>TODAY()-7,A1<TODAY()+1)

to avoid blanks and future dates
 
P

Phil

OK...I'm a rookie at this kind of thing......I'm missing something here....

Column "I" has a heading of "DATE DUE BACK" -- I would like to have I2 thru
I90 to turn RED 7 days before the date in that cell. I keep looking/typing
the formula in and trying differnet "minus # of days" to see if it
changes.....mmmmm........confusion on my end - IDEAS???

Thanks much!!!!

Phil
 
R

Ragdyer

I believe that you *are* missing COMPLETELY the initial instructions of
Cyber.

<<<"One Option, there are others- In Format>Conditional Formatting:">>>

Which means ... you are to use "Conditional Formatting"!
Which means ... you *don't* put the formula in the cells of Column I!
Is that perhaps what you did?

Anyway, with a quiet Sunday, and the lawn already mowed, would you like *3*
formats for Column I?
One for 7 days before due date,
One for due date,
And one for Past Due.

Start off by selecting I2 to I90.
While the cells are *still* selected, and with the focus of the selection in
I2 (colored white), click:
<Format> <ConditionalFormat>
Change "Cell value is" TO "Formula Is",
And enter this formula:

=AND(I2>0,I2>TODAY()-7,I2<TODAY())

Then click on "Format", and choose your font and pattern colors.
Then <OK>.

Click <ADD>, which allows you to set a second condition and format.
Click "Formula Is", and enter this formula:

=AND(I2>0,I2=TODAY())

Then click on "Format", and choose your font and pattern colors.
Then <OK>.

Click <ADD> again for your final condition and format.
Click "Formula Is", and enter this formula:

=AND(I2>0,I2>TODAY())

Then click on "Format", and choose your last font and pattern colors.
Then <OK> <OK>.
 
Top