Trying to get conditional formatting to highlight a range based on dates at the top of a column and whether this date is within specific start and end dates of various projects.
Have various projects with start and end dates for example:
Project Number Project Name Start Date End Date Apr-15 May-15 Jun-15 Jul-15
Example 1 Project 1 01/05/2015 30/06/2015
Example 2 Project 2 01/06/2015 30/06/2015
What I need is a conditional formula to highlight cells which are not in the range of the project.
So on Example 1 the cell in the columns Apr-15 & Jul-15 will be grayed out as the start date is 01/05/2015 to 30/06/2015.
Currently have the following IFAND formula in Excel which works:
=IF(AND(BI$4>=$E$6,BI$4<=$F$6),TRUE, FALSE)
Where BI$4 is the date on top of a column, E is the start date of the project and F is the end date of the project.
But when I try to put this in Conditional Formatting rule as:
=IF(AND(BI$4>=$E$6,BI$4<=$F$6))
I'm getting the formula you typed contains an error.
Any ideas as to how I can rephrase the formula to get this rule to work across a range?
Bookmarks