SOLVED

Conditional formatting traffic light system with blanks

New Contributor

Hi all,

 

I have been asked to create something and i'm really struggling to create the formula so i'm hoping someone here could help me out!

 

I currently have a formula in column J - =IF(E6-D6<=6,"N","Y") This tells me if a meter was handed over within our target of 6 days.

 

I now want to expand on this and create a traffic light visualisation so the admin staff can easily visually see what meters are almost late and can chase them up.

 

So in column I (status), the formula should be something like, =IF(E6-D6<=6, "GREEN IN TRAFFIC LIGHT", "RED IN TRAFFIC LIGHT").  However, if column E (handover date) is BLANK I want it to compare Column D (date) with cell A1 (todays date) and return the colours as white IF <=4 and amber IF value is between 5 and 6.  Hopefully that makes sense!

 

Many Thanks,

David

excel snip.png

4 Replies

@deemon1981 

And what if E6 is blank and D6 is more than 6 days before today's date?

@Hans Vogelaar 

 

Hi,

That would mean the meter has not been handed over in time and would be red.

best response confirmed by deemon1981 (New Contributor)
Solution

@deemon1981 

Thanks. See the attached sample workbook. I created a formula in column J that returns 1, 2, 3 or 4 and used those values in the traffic light conditional formatting rule.

@Hans Vogelaar 

 

Hans, you sir are a hero!  Your formula worked perfectly for me.  Thank you very much for your time and effort!  Have a great day!