Conditional formatting based upon due dates

  • Thread starter Thread starter Tracey G
  • Start date Start date
T

Tracey G

Hi,

I have a spreadsheet which maps out events which will occur by date.
I simply wish to add a RAG status indicating:-

Dates that are due within three days as AMBER
Dates that are overdue as RED
All other dates in the future as GREEN

I have no clue as to how to do this, please help.
Thanks,
Tracey
 
You may use Conditional Formatting for your desired results. In Office 2007
go to HOME and then STYLE and then CONDITIONAL FORMATTING. System will guide
you how to fetch results...
 
1. Select the range A1:A50. Please note that the cell referenced in the
formula is the active cell in the selection. Active cell will have a white
background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=A1>=TODAY()+3

Click Format Button>Pattern and select your color (say Green)

4. Click on Add button.

5. For Condition2>Select 'Formula Is' and enter the below formula
=AND(A1>=TODAY(),A1-3<TODAY())

Click Format Button>Pattern and select your color (say Amber)

6. Click on Add button.

7. For Condition3>Select 'Formula Is' and enter the below formula
=AND(A1>0,A1<TODAY())

Click Format Button>Pattern and select your color (say Red)

8. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.
 
Back
Top