Dates and Expiration formating

I am trying to do some conditional formatting in Excel with Dates. I have created a training tracking log which has the original date of training. I will try my best to describe what I need.


Original Date 1 year later 15 days out 30 days out 45 days out

3/15/2011 3/14/2012 2/28/2012 2/13/2012 1/29/2012
7/11/2010 7/11/2011 6/11/2011 6/11/2011 5/27/2011

Above is some sample dates. I need to change the ORIGINAL DATE Box Red when the time frame is 15 days before the annual expiration date. Orange at 30 days and yellow at 45 days.

What we are wanting is for it to give us allerts, so the first one will be at 45 days before retraining is due, the box will turn yellow and stay yellow until 30 days before retraining, then the box will turn orange to give us an alert that we have less time, and then at 15 days before retraining must be done it will turn red to tell us get this done now. Of course it will recycle when we enter the new date of training.

Please anyone that can help me with this I would be forever grateful. I have been trying to get this done for a couple weeks now and have read every college book I have on excel and searched the internet. Every avenue has been exhulsted. I have attached what I have of my spreedsheet.

Thank you in advance.