Conditional Formatting of dates using a traffic light system

Hi,

I have been given a register by a colleague containing a list of various plant owned by the company. As all the equipment needs to be calibrated yearly I wanted to use a traffic light system in conditional formatting. So if the item was due for calibration within 7 days it would be red. 30 days amber and anything over that would be green.

So far though I have come up with the following which sadly is not returning the results I had hoped for;

=IF(L6<>"",TODAY()-L6<7,TODAY()-K6=7) - Red
=IF(L6<>"",TODAY()-L6=30,TODAY()-K6=30) - Amber
=L6<>"" - Green

Column K = Last Calibration date
Column L = Calibration Due
Column T = Results (Red/Amber/Green)

Any help in showing me were I have gone wrong or a solution to my problem would be greatly appreciated

Regards
Taff

Hello Taff

I'm not sure if the 'Last Calibration Date' has any bearing on what you are trying to do, if you want to check the 'Calibration Due Date' with today's date. If you want a traffic light system for this then try:

=IF(L2="","",IF(L2-TODAY()<8,3,IF(L2-TODAY()<31,2,0)))

Here the due date is in cell L2 and the formula in M2. Set the traffic lights as in the attached image.

Hope this helps.
DBY

Hello DBY,

Thanks for the help you've saved me hours of trail and error on my part. It now works exactly how I wanted it to.

Kind Regards

Thanks All a lot it is clear