Formula to track expiration Dates by color

[helpmeexcelmasters]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->I am in the military and work in an armory. I have a spreadsheet in excel 2010 to track members qualifications. In column A I have all the names of everyone at the unit. Each column after that represents a type of qualification. For example column B is basic weapons quals. There is a different date for each person as they each were qualified at different times. The date listed is the date there qualification expires. I want to create a formula that will have the cell for everyone green when they are outside of 30 days from their expiration date, yellow when they are within 30 days of expiring and red on the date the expire (the date shown in cell).

So for example If cell B2 had a date of 11/29/11 it should be green until 11/1/11 when it would turn yellow and then on 11/29/11 it would turn red. There are a lot of peole and dates so I need a formula that I could copy and paiste that would work the same for each date.

I found some similar formulas surfing the internet such as using a conditional formatting formula of =(B2-TODAY())<31 and I choose a 3-color scale and changed the numbers around for the other two but when I try to save it it says no relative references in conditional formatting. I don't know if I am on the right track.

Can some one help me out?????

 

[yky]

1. select the cell you want the colors to be
2. go to conditional formatting
3. select "new rules"
4. choose "use a formula to determine which cells to format"
5. enter the following formula:

=IF((B2-TODAY())>31,TRUE,FALSE)

6. click on the "format" button
7. select "fill" and choose a color. If I understand you correctly, you should choose green here.

8. repeat step 1 to 7 but enter different formula and choose different fill color.

=IF(AND((B2-TODAY())<31, (B2-TODAY())>0),TRUE,FALSE)
Yellow color for the above formula???

=if((b2-today())<=0, true, false)
red color???

 

[helpmeexcelmasters]

You truely are an excel master!!! This worked perfect now the $64,000,000 question....Is there an easy way to copy these formulas to the entire spreadsheet without following that proccess for all of the cells individually? They all require the same type of notice but thay are all different dates.

 

[yky]

You truely are an excel master!!! This worked perfect now the $64,000,000 question....Is there an easy way to copy these formulas to the entire spreadsheet without following that proccess for all of the cells individually? They all require the same type of notice but thay are all different dates.

Click on the cell that contains the formula. Then, move the cursor to the bottom right hand corner. When the cursor turns into a solid black cross, press the left mouse button, drag across the cells you want to copy, then release the button.

 

[helpmeexcelmasters]

Thanks your Awsome!

 

[freckles81]

Hi

can someone please help me out, I am trying to do roughly the same thing but I am at a major loss here (been doing a huge sheet for hours now and the simplest thing has stopped me in my tracks.)

I want to look at a cell, say E2 and if the date in there has exceeded 9 weeks it turns that and the cells A2:D2 orange, exceeded 11 weeks it turns them red but I cannot figure out what I am doing wrong, its probably simple but I just can't see it!!

I tried the formula above but it doesn't work, I want it to look at the data in that cell and if the date you are currently at has exceeded 11 weeks it changes.

Please help a very frustrated young lady as I am about to cry!!

regards,
Kate