format

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
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
 
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
 
Cheers all

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

Bob
 
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
 
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

Frank Kabel said:
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
Click to expand...
 
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
 
I thought about that too, but I assumed he would set a new date. Good
addition, however.
 
Back
Top