how to color code experiation dates

[jamesbrewer]

I work for a trucking company and we have over 600 trailers in our fleet. Each Trailer has to have an annual inspection done each year. My excel set up is as followed Column A is just a list number, column B is the trailer unit number and column C is the date the Inspection is to Expire. What I am trying to so is if The date in Column C is 11/15/2014. I would like the date to change from being white to being yellow 120 days before the date, Then I would like it to Change from Yellow to Orange 45 days from the date, Then I would like it to turn Red 21 Days from the date. I have hundreds of these to do and Would also need to know how to copy the formula to all cells.

I know its a pain in the *** but if who ever is kind enough to answer my question would you email it to me my email is james@reynoldslogisticsinc.com

Thanks so Much,

James

[popipipo]

something like this?

[jamesbrewer]

Close but not exactly what im looking for. here is a sample of what im working with I am trying to get the Date Cells to color code based on when the next inspection is due. I have changed the colors manually but would like the dates to change them self. Thanks for all your help.

sample.xlsx

[mickgibbons1]

is this sorted?

[mickgibbons1]

hi mate

just go into condition formatting, formula and =(B2-TODAY())<120

just change the number per the colour you want.

if you want to double check by having the number of days count down then place this formula in cell C =B2-TODAY()

[Fotis1991]

It has come to our attention you have violated Rule 4 of our Forum RULES. Don't Private Message, Visitor message or email Excel questions to moderators or other members. (Or Access, Word, etc.)

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to permanent ban, so do take this caution to heart.

Post your question in a public thread and our many contributors will come to you to assist, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.

[jamesbrewer]

Thank you all for your help I have one more question it may be stupid, but it looks like I have to put in 3 formulas for each cell. Is there a way to copy the formulas so I don't have to do it all individually. Again, Thank you all for your help.

jamesbrewer

[FDibbins]

If you are applying the CF to a range, you can specify that entire range in the "applys to" part of the 1st CF window, or you can highlight the range, and then create the rules