Conditional Formula comparing two dates in separate cells and coloring a third cell?

[jmecobb]

Cell E6 has a start date of 2/6/13 and cell F6 has an end date of 6/11/13. Cell G6 through G17 are the months for the year 2013. I'm trying to create a formula that makes the cells that fall within those dates turn yellow and I searched for the past two days through this forum and have been unsuccessful. Would anyone be able to help me with this?

[mehmetcik]

Sample attached

[Tony Valko]

Cell G6 through G17 are the months for the year 2013.

Are those just the month names as TEXT entries or are they dates?

[jmecobb]

Tony, they are actual dates.

[Tony Valko]

Try this...

Select the *entire* range G6:G17 starting from cell G6.
Cell G6 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=AND(G6>=E$6,G6<=F$6)

Click the Format button
Select the desired style(s)
OK out

[jmecobb]

Hi Tony. Pretty cool, it worked but not 100% correctly. It colored all cell above/after month two. I attached the file if that makes it easier. Thank you for your help!

[Tony Valko]

OK, 1st thing...

Set calculation to Automatic. In the sample file it's set to Manual.

Use this formula in conditional formatting:

=AND(MONTH(G$5)>=MONTH($E6),MONTH(G$5)<=MONTH($F6))

Your dates on row 5 are the 13th of each month!

1/13/2013
2/13/2013
3/13/2013
4/13/2013
etc
etc

[jmecobb]

Tony, I bow to you. Thank you for your time and help. It works 100% perfectly and is exactly what I was looking for. Thanks again!

[Tony Valko]

You're welcome. Thanks for the feedback!