SOLVED

New Contributor

Conditional formatting traffic light system with blanks

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

4 Replies

Re: Conditional formatting traffic light system with blanks

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

Re: Conditional formatting traffic light system with blanks

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

Re: Conditional formatting traffic light system with blanks

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.

Re: Conditional formatting traffic light system with blanks

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!