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

Hi Phil-

One Option, there are others- In FormatConditional 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 |:)


"Phil" wrote:

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

Use a formula of

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

to avoid blanks and future dates


--
HTH

Bob Phillips

"CyberTaz" wrote in message
...
Hi Phil-

One Option, there are others- In FormatConditional 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 |:)


"Phil" wrote:

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

Thanks to both of you for the assist!!!!!

Phil

"Bob Phillips" wrote:

Use a formula of

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

to avoid blanks and future dates


--
HTH

Bob Phillips

"CyberTaz" wrote in message
...
Hi Phil-

One Option, there are others- In FormatConditional 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 |:)


"Phil" wrote:

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

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

"Bob Phillips" wrote:

Use a formula of

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

to avoid blanks and future dates


--
HTH

Bob Phillips

"CyberTaz" wrote in message
...
Hi Phil-

One Option, there are others- In FormatConditional 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 |:)


"Phil" wrote:

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

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

<<<"One Option, there are others- In FormatConditional 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(I20,I2TODAY()-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(I20,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(I20,I2TODAY())

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil" wrote in message
...
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

"Bob Phillips" wrote:

Use a formula of

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

to avoid blanks and future dates


--
HTH

Bob Phillips

"CyberTaz" wrote in message
...
Hi Phil-

One Option, there are others- In FormatConditional 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 |:)


"Phil" wrote:

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

Thanks for having the lawn mowed already....I'll give it a shot....

"Ragdyer" wrote:

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

<<<"One Option, there are others- In FormatConditional 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(I20,I2TODAY()-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(I20,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(I20,I2TODAY())

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil" wrote in message
...
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

"Bob Phillips" wrote:

Use a formula of

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

to avoid blanks and future dates


--
HTH

Bob Phillips

"CyberTaz" wrote in message
...
Hi Phil-

One Option, there are others- In FormatConditional 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 |:)


"Phil" wrote:

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