Conditional Formatting by Date Help Needed

[blindsighted]

Greetings,
I am using Excel 2007. I need to conditionally format a worksheet. Specifically a column of dates. The sheet is to show the Student's Names and Dates of Birth. I need to make all the dates between January 1 and September 30 (regardless of year) to be light green cells and students with dates of birth October 1 through December 31 to be a different color. The date format I have in that column is mm/dd/y yyy. I believe I would need to choose the option in Conditional Formatting that allows me to put in a formula. However, I do not know the correct formula/expression to enter. Can one of you gurus tell me the correct formula for my task?

 

[Robert Mika]

Excel 2010

	A	B	D	E	F
1	1/1/2011		1/1/2012	10/1/2012	12/12/2012
2	2/1/2011
3	3/1/2011
4	4/1/2011
5	5/1/2011
6	6/1/2011
7	7/1/2011
8	8/1/2011
9	9/1/2011
10	10/1/2011
11	11/1/2011
12	12/1/2011
13	1/1/2012
14	2/1/2012
15	3/1/2012
16	4/1/2012
17	5/1/2012
18	6/1/2012
19	7/1/2012
20	8/1/2012
21	9/1/2012

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Hello and welcome.
Put dates(any Jan,Oct and Dec) as in example then:
light green
=AND(MONTH($A1)>=MONTH($D$1),MONTH($A1)<=MONTH($E$1))

any other
=AND(MONTH($A1)>=MONTH($E$1),MONTH($A1)< =MONTH($F$1))

(I'm using US date format so the months are in front)

 

[blindsighted]

Thank you for your reply. Can I make those cell references you listed in D, E and F on another sheet so that they do not show up on the sheet one? If so, how would that affect the formula you listed?

 

[Robert Mika]

Like this:
=AND(MONTH($A1)>=MONTH(Sheet2!$B$1),MONTH($A1)<=MONTH(Sheet2!$C$1))
if you want to move the dates to Sheet2

 

[blindsighted]

Thanks for your help, however, that did not work. I don't think I have enough understanding in Excel to do this. I think I needed someone to tell me like a 9 year old or something I have tried online tutorials and read 41 articles, but they are written and designed for persons who know or understand code and the two books I have on the subject do not explain how to the task I need. I am a novice.What else can I try?

 

[Robert Mika]

Try this:
https://dl.dropbox.com/u/78583609/cf.xlsx

 

[blindsighted]

I tried it but it said that i cannot reference other sheets or workbooks in conditional formatting. I would think that would be very limiting of Excel to disallow that function.

 

[Robert Mika]

I tried it but it said that i cannot reference other sheets or workbooks in conditional formatting. I would think that would be very limiting of Excel to disallow that function.

Create the Dates in the Sheet where you got your data.
Create Conditional Formatting then cut and paste the cells with Dates into another sheet.