Color coding dates in excel

[Hilly]

Hi ,
I am trying to set upo a spreadsheet to track stock market
purchases,dividends etc.
I have date columns, however I would like to be able to color code the dates.
Say blue for dates between 01/07/2006 and 30/o6/2007
red for 01/07/2007 to 30/06/2008
green for 01/07/2008 to 30/06/2009 etc

is there a formula I can input to set this up?

 

[Stefi]

Conditional formatting is the facility doing such jobs. In Excel2003 and
below you can set max 3 conditions, in Excel2007 there is no limit.

Regards,
Stefi

„Hilly†ezt írta:

 

[Hilly]

Thanks Stefi,

But being new tothis , I would not have a clue on how to write a formula to
achieve the required result. I have tried to input things into the formula
line, butr it is goggle di gook to me, Do you know how to go about it?

 

[Stefi]

If you want to color only the date cells, then
select date cell range,
Format>Conditional formatting>Cell value:between and enter 01/07/2006 and
30/06/2007 in from and to fields respectively!
Choose a blue pattern!
etc.

If you want to color several cells in rows of date cells matching your
criteria, then
select range to be colored, say A2:Z100, dates being in column A
Format>Conditional formatting>Formula:
=AND($A1>=DATEVALUE("01/07/2006"),$A1<=DATEVALUE("30/06/2007"))

Choose a blue pattern!
etc.

Regards,
Stefi


„Hilly†ezt írta:

 

[Hilly]

Thanks Stefi,

Your answer was somewhat helpful.
I actually found a conditional format button on the tool bar , then went to
"cell value between", inserted the dates and then chose the color.

If that isa the same as what you wrote, ie If you want to color several
cells in rows of date cells matching your
criteria, then
select range to be colored, say A2:Z100, dates being in column A
Format>Conditional formatting>Formula:
=AND($A1>=DATEVALUE("01/07/2006"),$A1<=DATEVALUE("30/06/2007"))

Choose a blue pattern!

That is great.

I just have one question. If I was to go about it as you suggested, where do
I start. If I right click and select format, I cannot write any formula in
there. So where do I actually go if I wanted to write the formula as you
suggested??

 

[Stefi]

In Excel2003 (post if you use Excel2007):
Format>Conditional formatting>In the format conditions dialog you'll see 1st
condition and below it a field containing Cell value with a drop down arrow.
Click on the arrow and choose Formula from the drop down list. Enter formula
into the field next to it!

Stefi


„Hilly†ezt írta:

 

[Hilly]

Sorry Stefi ,

I forgot to mention that I am using Excel 2007

 

[Stefi]

In Excel2007 (sorry if my terms are not the same as in the English version,
I'm translating back from Hungarian):

Start Page>Conditional formatting>Cell selection rules (1st option)>More
rules (last option)>Selection by formula (last option)

Regards,
Stefi


„Hilly†ezt írta: