The screenshot shows some November dates known as Expiry Date. I want to use conditional formatting to automatically highlight any dates that are within 7 days of today's date. This should be volatile so that every day it will keep checking the current date and highlighting any cells that are now within 7 days of this. Do I need to create a separate calculation such as datediff or what other way would be best to tackle this problem.
Thanks
Shirley
Hi Shirley,
Try this:
1. Select your range of dates, e.g. A2:A20
2. Click Format -> Conditional Formatting...
3. Select 'Formula Is'
4. Type (or copy/paste) this formula:
=AND(TODAY()-7<=A2,TODAY()+7>=A2)
5. Set the format you want (highlite format, etc.)
6. Click OK
Select the range of cells containing dates and click on Format, Conditional Formatting. Leave "Cell Value Is" and "between", then enter =TODAY() in the next field and =TODAY()+6 in the last field. Set the format to be applied, then click on OK to return to your worksheet.
Leave the cells selected because Excel will add quote marks to the formulas, like this: ="TODAY()", which prevents the formatting from working. Go back into the conditional formatting wizard and delete them.
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
Try conditional fomatting
highlite the range
go to format,conditional formatting
1st box select
cell value is
2nd box select
less than
3rd box enter this
=NOW()-7
select the format button and select the type of format you want if the condition is met
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks