Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
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? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
Sorry Stefi ,
I forgot to mention that I am using Excel 2007 "Stefi" wrote: 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? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Color coding dates in excel
In Excel2007 (sorry if my terms are not the same as in the English version,
I'm translating back from Hungarian): Start PageConditional formattingCell selection rules (1st option)More rules (last option)Selection by formula (last option) Regards, Stefi €˛Hilly€¯ ezt Ć*rta: Sorry Stefi , I forgot to mention that I am using Excel 2007 "Stefi" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
color coding | Excel Discussion (Misc queries) | |||
color coding | Excel Discussion (Misc queries) | |||
color coding | Excel Discussion (Misc queries) | |||
Color coding the excel sheets | Excel Discussion (Misc queries) | |||
Color coding cells in Excel | Excel Discussion (Misc queries) |