Conditional Formatting between dates that don’t span a whole year

[DocDeb]

Using Excel 2010. I can make Conditional Formatting work great between dates that DO span a whole year. But try to split a year into two and it won’t work. My fiscal year (FY) starts October 1 and ends September 30 of the next year. I want to be able to enter a date for any FY and have the cell change to the color assigned for that FY. Is there a way to do that?

 

[AhoyNC]

Might help if you could post formula you are using in conditional formatting.

The example below is working for me. Is this what you are trying to do?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Beg. Date	3/1/2013
3	End Date	3/1/2014
4
5	1/1/2013	2/1/2013	3/1/2013	4/1/2013	5/1/2013	6/1/2013	7/1/2013	8/1/2013	9/1/2013	10/1/2013	11/1/2013	12/1/2013	1/1/2014	2/1/2014	3/1/2014	4/1/2014	5/1/2014
6
7
8		Formula used in Conditional Formatting
9		=AND(A5>=$B$2,A5
10
Sheet

 

[DocDeb]

Might help if you could post formula you are using in conditional formatting.

You're right! It would help if I posted the formula I was using. I'm using a simple method of Format only cells that contain > Cell value > between > 10/1/2012 and 12/31/2012, Format only cells that contain > Cell value > between > 1/1/2013 and 9/30/2013...etc. It has worked great in the past for whole years, but now that we are on Fiscal years, it won't work that way (at least for me).

 

[AhoyNC]

What you did worked for me in Excel 2010.

Try this.
Go to Conditional Formatting
Choose MANAGE RULES
Then from the drop down box choose THIS WORKSHEET
Highlight your rule and click on EDIT
Make sure Excel has not put quotation marks around any of your dates.