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..

In 2007 Also..

"Knee2no" wrote:

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..

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

On Aug 13, 4:10*pm, Knee2no wrote:
In 2007 Also..



"Knee2no" wrote:
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..- Hide quoted text -

- Show quoted text -

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" wrote:

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

On Aug 13, 4:10 pm, Knee2no wrote:
In 2007 Also..



"Knee2no" wrote:
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..- Hide quoted text -

- Show quoted text -

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

On Aug 13, 10:51*pm, Knee2no
wrote:
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" wrote:
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

On Aug 13, 4:10 pm, Knee2no wrote:
In 2007 Also..

"Knee2no" wrote:
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..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -