Conditional Formatting between two dates

Question

I am creating an Early Intervention Program spreadsheet that will keep track of disciplinary actions in Excel 2010. I have multiple categories (columns) of defensible actions. I want to color code the cell based on the date that was entered in column A, but is checked against the effective date of the program (cell B2). One column's criteria is if the date is between A4 and the next consecutive 365 days.

How do I setup that in conditional formatting?

Answer 1

... One column's criteria is if the date is between A4 and the next consecutive 365 days.

Let's suppose that you want to color code any date in column D that is greater than or equal to the date in A4 AND less than the date in A4 plus one year.

Select D1 and tap Ctrl+<spacebar> to select all of column D with D1 as the active cell. Create a new Conditional Formatting Rule with Use a formula to determine which cells to format and supply the following for the Format values where this formula is true: 

=AND(ISNUMBER($D1),$D1>=$A$4,$D1<EDATE($A$4,12))

Click Format and select a Fill for the color code. Click OK to accept the formatting and then OK again to create the new rule.

AND function
ISNUMBER function
EDATE function