Tracking Dates For Future Occurrences

[Knee2no]

Can this be done? I want to track a yearly review. I would like the date,
once entered - say 6/1/2009, to conditionally format to change yellow 30 days
before, then red 15 days before, and then to stay red until the date is
updated again for say 6/1/2010. Can this be done? I am new to all this,
thanks..

 

[Knee2no]

In 2007 Also..

 

[JP]

This is just air code to get you started.

This formula returns the current date. For example, 8/13/2008 is
serial number 39673.

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

This formula returns the serial number for June 1st of the current
year (i.e. it *should* auto-adjust for 2009)

=DATEVALUE("6/1/" & YEAR(NOW()))

This formula returns 39600 for 6/1/2008.

So if you subtract the second number from the first, you get the
difference in days (negative numbers means the number of days past 6/1
of the current year). Now you can simply use them in the conditional
formatting box to set up your cell coloring. The formula that checks
30 days out should come first, followed by 15 days out, etc...


HTH,
JP

 

[Knee2no]

This is a great start, but I am confused on setting up the formula's in the
conditional formatting. Sorry, I have nearly no experience..

 

[JP]

Sorry I may have led you in slightly the wrong direction. You wanted
to enter a date in a cell and check how far it is from June 1st of
whatever year it happens to be.

First I went to the name box (Ctrl-F3) and created a named constant
"DateToChecK' and in the "Refers to" box I entered:

=DATEVALUE("6/1/" & YEAR(NOW()))

Then I selected cell H2 and entered the following formula in the
conditional formatting box:

=IF((H2-DateToCheck)<=30,TRUE,FALSE)

Then set a pattern of Yellow background for the cell.

Then I added a second condition by clicking the "Add >>" button and
entered this formula:

=IF((H2-DateToCheck)<=15,TRUE,FALSE)

For that condition I set a pattern of Red background for the cell.

Keep in mind this is for Excel 2003. Apologies if it doesn't work in
2007.

Here are some great samples:
http://www.cpearson.com/excel/cformatting.htm
http://www.contextures.com/xlCondFormat02.html

Then you can test it out by entering dates in the current year that
are before 6/1 (in this case, 2008). When you get 30 days out, the
cell turns yellow. When you get 15 days out, it turns red. Once you
get past the date, of course, it should change back to normal.

HTH,
JP