Conditional formatting to RAG rate based on dates

DebsTurner

New member
Joined
Oct 5, 2016
Messages
4
Reaction score
0
Points
0
I have a column that holds a date (F2). This is the "received" date. Then i have another column (I2) that i work out and input a "target" date that is 20 working days after that in F2. What i want is when I2 is between 0 - 10 working days from F2 to be green, 11 - 20 working days to be amber, and when it's 21 days and over, to be red. Does that make sense?

Obviously i would like the "fill" to be rag rated, not the font, but i don't know how to do that on here :smile:
Many thanks in advance.
Debbie.
F2I2
04/09/1630/09/16
16/09/1614/10/16
25/09/1621/10/16
 

navic

New member
Joined
Aug 27, 2013
Messages
901
Reaction score
0
Points
0
Excel Version(s)
Excel 2013
Obviously i would like the "fill" to be rag rated, not the font
:confused:
If you had been set an example with the expected result, it would probably be clearer.
Try Conditional Formatting
GREEN
Code:
=DAY(E1)<11
AMBER
Code:
=AND(DAY(E1)>10;DAY(E1)<21)
RED
Code:
=DAY(E1)>20
 

SUNNY KOW

New member
Joined
Jul 24, 2016
Messages
41
Reaction score
0
Points
0
Location
Malaysia
Hi Debs
You can try Conditional Formatting
e.g.

=I2-F2>=21 for RED
=AND(I2-F2>=11,I2-F2<=20) for AMBER
=I2-F2<=10 for GREEN

Refer attachment.
Hope this is what you are looking for.
 

Attachments

  • DebsTurner.xlsx
    9 KB · Views: 113

DebsTurner

New member
Joined
Oct 5, 2016
Messages
4
Reaction score
0
Points
0
Hi. Thanks both for your offer of help. It's much appreciated. I can't however get any of them to work. Do they recognise that it's "working days" i need, rather than a straight run?
 

navic

New member
Joined
Aug 27, 2013
Messages
901
Reaction score
0
Points
0
Excel Version(s)
Excel 2013
Workday in Excel and Conditional Formatting

I can't however get any of them to work.
Please put your workbook example with results expected.

BTW: Try formula for calculation workday
Code:
=NETWORKDAYS(startDate;endDate)+IF(NETWORKDAYS(startDate;endDate)<0;1;-1)+1
 

DebsTurner

New member
Joined
Oct 5, 2016
Messages
4
Reaction score
0
Points
0
OK. I've come to realise I've gone about this entirely the wrong way, and I've not explained myself very well at all. Thanks to those who have taken the time to post help thus far. This is what I want the formula to say in cell I2 -

If today is greater than or equal to the date in F2 and is less than or equal to the date in H2, turn green.

If today is equal to H2+1 but less than or equal to I2, turn amber.

If today is greater than I2, turn red.

God I hope that makes sense to somebody.
 
Top