Function re:Dates in Conditional Formatting

[Guest]

Hello,

I'm using a formula in conditional formatting and having some trouble.

I have a spreadsheet of expiration dates. I want the spreadsheet to tell me
when I am 70 days or less from the expiration date by highlighting the date
cells yellow. The reason is, I have to make sure I'm notified this far in
advance so I can start submitting paperwork to renew contracts before their
expiration date.

Ex:
cell E2 05/31/07
cell E3 09/08/08

I used the following formula in conditional formatting:
=(TODAY()-E2<=70) meaning...take today's date minus date in E2..if the
resulting # is less than or = to 70...highlight the cell yellow.

This works for cell E3 b/c as of today, 5/11/07, I'm 70 days or less than 70
days from the expiration date but it highlights cell E3, too, when I try
copying E2's formatting to E3.
E3's date is more than a year away...E3 is certainly more than 70 days from
today's date.
What am I doing wrong?

Thank you,
Studebaker

 

[Guest]

It should be the other way around,
ie: E2-TODAY()<=70

But its better to include a check to exclude any blank cells in col E,
viz, use instead as the cond format formula in E2:
=AND(E2<>"",E2-TODAY()<=70)

Note that you could always apply the CF at one go by selecting the range
say, E2:E10 (with E2 active), then just paste the formula above (which points
to the active cell E2 in the range) in the "Formula Is:" box