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,


excel snip.png

4 Replies


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

@Hans Vogelaar 



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

best response confirmed by deemon1981 (New Contributor)


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!