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?

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:

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?

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

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:

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?

If you want to color only the date cells, then
select date cell range,
FormatConditional formattingCell 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
FormatConditional formattingFormula:
=AND($A1=DATEVALUE("01/07/2006"),$A1<=DATEVALUE("30/06/2007"))

Choose a blue pattern!
etc.

Regards,
Stefi


€žHilly€� ezt Ã*rta:

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

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:

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?

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
FormatConditional formattingFormula:
=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" wrote:

If you want to color only the date cells, then
select date cell range,
FormatConditional formattingCell 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
FormatConditional formattingFormula:
=AND($A1=DATEVALUE("01/07/2006"),$A1<=DATEVALUE("30/06/2007"))

Choose a blue pattern!
etc.

Regards,
Stefi


€žHilly€� ezt Ã*rta:

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

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:

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?

In Excel2003 (post if you use Excel2007):
FormatConditional formattingIn 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:

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
FormatConditional formattingFormula:
=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" wrote:

If you want to color only the date cells, then
select date cell range,
FormatConditional formattingCell 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
FormatConditional formattingFormula:
=AND($A1=DATEVALUE("01/07/2006"),$A1<=DATEVALUE("30/06/2007"))

Choose a blue pattern!
etc.

Regards,
Stefi


€žHilly€� ezt Ã*rta:

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

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:

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?