Conditional formatting to 'traffic light' a review date

[VMerry]

Hello,

I am using Excel 2007 and have a set of review dates for a number of people. Each person must have their next review within 10-12 weeks. I'm trying to set a conditional format on the next review date with the following;

* if less than 90 days - highlights green
* if less than 40 days - highlights amber
* if less than 20 days - highlights red

I've been reading help pages and playing around for a while now. I've tried a number of formulae within the conditional formatting, the closest I managed was with the series below but I only seem to get the cells to highlight red once they are all entered.

=OR(TODAY()-$N$2<=-90,$P$2<>"")
=AND(TODAY()-$N$2>-40,TODAY()-$N$2<0)
=TODAY()-$N$2>0

I'm well and truly stuck so any help would be gratefully received!

Thanks for taking the time to read this

 

[mumps]

If you are willing to try a macro, this might do it assuming your dates are in column N.

Sub Test()
    Dim bottomN As Integer
    bottomN = Range("N" & Rows.Count).End(xlUp).Row
    Dim x As Long
    For x = bottomN To 2 Step -1
        If Cells(x, "N") - Date > 40 And Cells(x, "N") - Date <= 90 Then
            Cells(x, "N").Interior.ColorIndex = 4
        ElseIf Cells(x, "N") - Date > 20 And Cells(x, "N") - Date <= 40 Then
            Cells(x, "N").Interior.ColorIndex = 6
        ElseIf Cells(x, "N") - Date > 0 And Cells(x, "N") - Date <= 20 Then
            Cells(x, "N").Interior.ColorIndex = 3
        End If
    Next x
End Sub

 

[p45cal]

What date is in column N? Is it the date the review is going to happen? Is it the date of the last review?
What's in column P?
Which column do you want the conditional formatting to appear in?

 

[VMerry]

What date is in column N? Is it the date the review is going to happen? Is it the date of the last review?
What's in column P?
Which column do you want the conditional formatting to appear in?

Hi p45cal,

Column N is the date of the last review, column P is the date of the planned review (12 weeks after). I was hoping to make column P a traffic light system but couldn't make it work so thought I would settle for column O changing colour?

I'm open to suggestions is some part of the row will highlight when the review is imminent.

Thanks, VMerry

 

[VMerry]

Hi Mumps,

I've seen macro's before but never done one myself. I'll play around and see if I can get it to do the job
Thanks for the idea!

VMerry

 

[p45cal]

Excel Workbook
	P
1	planned review
2	08/03/2013
3	14/03/2013
4	20/03/2013
5	26/03/2013
6	01/04/2013
7	07/04/2013
8	13/04/2013
9	19/04/2013
10	25/04/2013
11	01/05/2013
12	07/05/2013
13	13/05/2013
14	19/05/2013
15	25/05/2013
16	31/05/2013
17	06/06/2013
18	12/06/2013
19	18/06/2013
20	24/06/2013
21	30/06/2013
22	06/07/2013
23	12/07/2013
24	18/07/2013
25	24/07/2013
26	30/07/2013
27	05/08/2013
28	11/08/2013
29	17/08/2013
30	23/08/2013
31	29/08/2013
32	04/09/2013
33	10/09/2013
34	16/09/2013
35	22/09/2013
36	28/09/2013
37	04/10/2013
38	10/10/2013
39	16/10/2013
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2	1. / Cell Value less than =TODAY()+20	Abc
P2	2. / Cell Value less than =TODAY()+40	Abc
P2	3. / Cell Value less than =TODAY()+90	Abc

When adding CF, choose the Format only cells that contain option (because we're highlighting the cells that themselves contain the planned date), then in Format only cells with: fields use Cell Value, less than, the formula in the screenshot above respectively for each of the three colours, in that order, and don't have the Stop if true checkboxes checked.

 

[VMerry]

Hi p45cal,

It worked! THANK YOU!