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?

 

[Ganjin]

nvm, need a minute xD

 

[Ganjin]

alrighty, then

first, select the column that the dates are in, then:

=IF($A1>NOW(),TRUE,FALSE)
=IF(AND(MONTH($A1)=MONTH(NOW()),YEAR($A1)=YEAR(NOW())),TRUE,FALSE)
=IF(AND($A1<NOW(),$A1<>""),TRUE,FALSE)

 

[A Prince]

Sorry, not following that. Where do I paste that or copy it? Right now column F, cells 10 thru 34 have different months in them. I can of course select column F, but not sure how to enter the formula. If I go into conditional formating I get lost real quick. BTW I am using Office 2010 if that matters.

Thanks for trying to help out...

 

[Ganjin]

ah right, the formula I gave you works for column A,

you will need to change $A1 based on the column that the data is in.
so if the data is in Column F:
$A1 becomes $F1

okay, so here is how to do it,
1) select the Column (in this case Column F)
2) Conditional Formating, New Rule
3) Use A Formula To Determine Which Cells To Format
4) Format Values Where This Formula Is True:
5) =IF($F1>NOW(),TRUE,FALSE)
6) Format (set the Background Color to a Red shade)
7) hit Okay
8) New Rule, Repeat Steps 4 - 7 (changing the formula and background color accordingly)

 

[Ganjin]

ah, back to the original formulas, it looks like I made a mitake

Instead of:
=IF(AND($A1<NOW(),$A1<>""),TRUE,FALSE)
Use:
=IF(AND($A1<NOW(),$A1<>""),IF(MONTH($A1)=MONTH(NOW()),FALSE,TRUE),FALSE)

 

[A Prince]

Well, I didn't have any trouble entering the formula but it did not seem to do anything.

 

[Ganjin]

sorry didn't mean to double post

 

[Ganjin]

for Column F the following applies:

=IF($F1>NOW(),TRUE,FALSE)
=IF(AND(MONTH($F1)=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),TRUE,FALSE)
=IF(AND($F1<NOW(),$F1<>""),IF(MONTH($F1)=MONTH(NOW()),FALSE,TRUE),FALSE)

 

[Ganjin]

I went from 1990 to 2028 to check this silly thing and found a small problem, this should fix it.

=IF($F1>NOW(),TRUE,FALSE)
 
=IF(AND(MONTH($F1)=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),TRUE,FALSE)
 
=IF(AND($F1 < NOW(),$F1 <> ""),IF(AND(MONTH($F1)=MONTH(NOW()),YEAR($F1)=YEAR(NOW())),FALSE,TRUE),FALSE)