Conditional formatting a table cell based based another table cell for project start end dates

0
votes

I am trying to build a project planning doc that automatically highlights the month cells based on the start and end dates of the project.

Currently I have entered the formula in the cell... But, I would like the formula to be embedded in the conditional formatting command itself so that I can have other info on the cell...

START   END Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

3/5/2014    9/15/2014

Currently I am using the below formula in the cell. Instead I would like to have this formula embedded in the conditional formatting as a formula to highlight the respective cell.

C2 = IF(AND($C$1<=$B2,$A2<$D$1),"Overlap","Do not overlap")
D2 = IF(AND($D$1<=$B2,$A2<$E$1),"Overlap","Do not overlap")
2
excelexcel-formulaworksheet-functionconditional-formatting

2 Answers

2
votes

Assuming START is in A1, please select ColumnsC:N C2 to as far down ColumnN as desired and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:: 

=AND(C$1<=$B2,$A2<D$1)

Format..., select your choice of formatting, OK, OK.

The add a new rule: 

=AND(C$1<=$B2,$A2<D$1)

The result should look like:

SO18718744 example

where I chose red fill for both rules. Note that the dates in Row1 are each the first of the month shown, for 2014.

0
votes

If A2 contains your start date and B2 contains your end date and D2 is the date you want checked enter this formula based conditional formatting.

=AND(D2<=$B$2,D2>=$A$2)