format

[Bob]

Hi

I require a cell to show as shaded if a condition is met, but cant seem to
get conditional formatting to work.

Example:

Review date 5/2/2004 ( UK format. 5th Feb)

Requirement:

If this date is equal to or less than 7 days from today's date, shade cell
grey......... basically I want and early warning 7 days before my student
review days !!

I have a spare cell on the sheet to insert the function "NOW" if this is
needed to aid the other function

TIA

Bob

 

[Frank Kabel]

hi Bob
Select the cell and goto 'Format - conditional format' and enter the
following formula as condition (assumption: A1 is your cell:
=A1>=TODAY()-7
replace A1 with your actual cee reference and select your format

HTH
Frank

 

[Tom Ogilvy]

Assume the review date is in the cell with the conditional formatting.

Cell value is Less than or Equal to = today()+7

or use =now()+7

 

[Bob]

Cheers all

Do I assume I have to have the spare cell with TODAY in it for the function
TODAY to work

Bob

 

[Frank Kabel]

Hi Bob
you don't need your spare (helper) cell. Just select your target cell
(that is the cell you want to shade) and enter the formula (replacing
A1 with the reference of your target cell).
Frank

 

[Tom Ogilvy]

If A1 contained Mar 31, it is greater than Today - 7, but today isn't
within 7 days of Mar 31. I think you have your condition backwards.

Shouldn't it be

=A1<=Today()+7

or see my post.

--
Regards,
Tom Ogilvy

Hi Bob
you don't need your spare (helper) cell. Just select your target cell
(that is the cell you want to shade) and enter the formula (replacing
A1 with the reference of your target cell).
Frank

 

[Frank Kabel]

Hi Tom
you're right, I misread the post. But I would add the following to your
suggestion (to prevent shading after the day in A1 is reached):
=AND(A1<=Today()+7,A1>=Today())

This will shade all day in A1 between today (inlcuded) and today+7

Regards
Frank

 

[Tom Ogilvy]

I thought about that too, but I assumed he would set a new date. Good
addition, however.