Cell coloring auto change by date reference

[A Prince]

I have something that I track that needs to take place in a given month and I need a visual clue that the month is approaching. What I would like is the month before I need to take action, that cell turns green. If I do not take any further action I would like that cell to turn yellow when the month that the action is due comes around and then turn red once that month has passed. The target month can be ANY month of the year, so the formula needs to be able to distinguish between all of the months and react accordingly.

So if the target month was Jun-11 then on May 1st 2011 the Jun-11 cell would turn green. ON Jun 1st the cell would turn Yellow, and on Jul 1st turn red. The next line I am tracking might have Feb-12 entered. So on Jan 1st of 2012 the Feb cell would turn green and on Feb 1 turn yellow and Mar 1 turn red.

Clear as mud?

 

[A Prince]

When I entered the formula some cells changed and some didnt. Here is what I had in the cells, all of which are in F column.

F10 Apr-11
F11 Feb-11
F12 Oct-11
F13 Nov-11
F14 Feb-12

None of thsse cells reacted to the formula.

However all of the rest of the cells F15 thru F26 reacted even though none of those cells were within one month of March which is when I need it to trigger as this is March.

F15 was Dated Dec-11 and should not have reacted until the present month was Nov, but it reacted to the formula. (I formatted it to change the font to red)

F16 was dated Nov-11 and for it to do what I would like, it should not react until Oct.

I have data in columns E,F,G,I and K that I would like to moniter and have each cell react the month before the date in the cell, a different action when the cell and the current month are the same and a different result when the present month is the month after the month in the cell. Is this even possible?

 

[Ganjin]

you are probably doing something wrong, because regardless of the column, I get it to work fine:

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">May-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Jun-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Jul-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Aug-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Sep-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Oct-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Nov-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Dec-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Jan-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Feb-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(247, 150, 70);" align="right" height="20">Mar-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Apr-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">May-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Jun-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Jul-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Aug-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Sep-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Oct-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Nov-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20"> </td> </tr> </tbody></table>

 

[A Prince]

Your example is not doing what I would like to happen. This is the month of March, so only cells with a date of Feb, Mar or Apr should react. If it is to work the way I would like it to work, Feb cell would be red, Mar cell would be yellow and Apr cell would be green. Only cells from Feb, Mar or April would react. Next month (Apr) cells dated Mar would turn red, Apr cells would turn Yelllow and May cells would turn green.

 

[Ganjin]

-____-

Column F

=IF(AND(MONTH($F1)+1=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),TRUE,FALSE)
=IF(AND(MONTH($F1)=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),TRUE,FALSE)
=IF(AND(MONTH($F1)-1=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),TRUE,FALSE)

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" align="right" width="64" height="20">Dec-10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">Jan-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(192, 80, 77);" align="right" height="20">Feb-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(247, 150, 70);" align="right" height="20">Mar-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(155, 187, 89);" align="right" height="20">Apr-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">May-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">Jun-11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">Jul-11</td> </tr> </tbody></table>

 

[A Prince]

I must be doing something wrong. I copied your formula. Selected column F. Selected conditinal formatting. Selected New Rule then "Use a formula to determine". I then pasted the formula into the block "Format values where this formula", etc and clicked OK. Nothing seems to happen....

 

[Ganjin]

hmm ...
okay, when your selecting Column F does the cursor change into a down arrow?
if it does try this:
1) conditional formatting, manage rules (if it is empty you are fine), delete any rules that are there
2) new rule, use a formula ..., paste one of the formulas:
=IF(AND(MONTH($F1)+1=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),TRUE,FALSE)
3) format, fill, background color, pick the appropriate color (for the above formula red)
4) hit okay until you are back at the conditional formatting rules manager
5) repeat steps 2 to 4 until all three formulas are included
6) hit okay and the formatting should be in effect

err, lets see, these should explain more on conditional formatting:
http://www.exceluser.com/solutions/custtasks.htm
http://www.java2s.com/Tutorial/Micr...Format-Style/0200__Conditional-Formatting.htm

they should cover what you need to know, but someone else might be able to provide a better resource.

 

[A Prince]

Let me go try that! I was entering everything as one formula, not 3 different formulas. My duh!!