Alerting imminent dates in excel using conditional formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up a document review alert system
Column A = Document ref No.
Column B = Document Title
Column C = Document review date (input by user)

I am using the typical Red, Amber, Green system

Therefore:
if today is more than 60 days prior to review date then no formatting
if today is between 60 to 31 days prior to the review date then the cell
will be green
if today is between 30 - 1 day prior to the review date then the cell will
be amber
if today is either 0 or past the review date then the cell will be red

How do I achieve this? (please specify whether it's a formula or an array as
I'm easily confused). Ideally this would auto calculate each time the
spreadsheet is opened.

Thanking you all in anticipation of your assistance

Terry
 
Tel said:
Therefore:
if today is more than 60 days prior to review date then no formatting
if today is between 60 to 31 days prior to the review date then the
cell
will be green
if today is between 30 - 1 day prior to the review date then the cell
will
be amber
if today is either 0 or past the review date then the cell will be red
Click to expand...

Highlight the cells with the dates.

Select Format\Conditional Formatting

You can set your criteria by selecting Cell Value Is\
Between\=now()+31\=now()+60 etc.

Play around with it and you will work it out.

Matt
 
Please help me out, Ive been going nuts for 3 months how to get thi
done right. Ive got a spreadsheet with employees listed in column A
columns B, C. & D all have dates of various physicals (ie 08/01/06)
etc. How can I conditionally format these cells to turn a color 3
days prior to the yearly renewal dates (ie 08/01/07)
 
Back
Top