Conditional Formatting

Question

Dear All,

I have dates on a spreadsheet which indicate when an individual passed a test. The result expires two years from this date at which point the individual needs to be tested again.

I need conditional formatting rules to distinguish between ‘in date’, expiring and expired results. For this request, the date is in cell B2.

I have the following conditions.

1. In date (Green)
   =$B2>=(TODAY()-730)
   This works.
2. Expired (Red)
   =$B2<=(TODAY()+730)
   This works.
3. Expiring (Yellow)
   This should kick in on day 640 or 90 days from expiry.
   =AND(=$B2-TODAY()>=640,$B2-TODAY()<=730)
   This does not work.

The hierarchy is:
• ‘in date’
• Expiring
• Expired

I would be grateful for some assistance.

Answer 1

Hi HansV,

I was able to get the conditional formatting to work when I changed the hierarchy. New hierarchy is expired, expiring and in date. Previously, it was the other way round. All "stop if true" boxes were checked. Thank you for your assistance.

Answer 2

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 3

Hi HansV,

Many thanks for your response. I have tried out your suggestion. Unfortunately, it hasn't worked. The expired and expiring dates are highlighted yellow. What am I doing wrong?

Answer 4

Set the fill color to green - this will be the default.

Create 2 rules:

First

=$B2<=EDATE(TODAY(), -21)

and yellow for expiring in the next 3 months

Second:

=$B2<=EDATE(TODAY(), -24)

and red for expired.