I have a formula in B2 to display the date that appears in A2, plus 7 days. I typed "7" into Sheet 2, C2, and refer to that cell in the formula.



Now I would like to highlight B2, if today's date is more than 7 days past the date in A2. In other words,

A2 = 6/1/14
B2 = 6/8/14 with formula: =IF(A2="","",(SUM(A2+Sheet2!$C$2)))
Today's date is 6/27/14, so B2 needs to be highlighted in red.

Is there any way to do that?

Thanks!

This formula entered into Conditional Formatting should do it:

=TODAY()>B2

The formula tests whether the current date is past the date in Cell B2. If the statement is true, it applies the formatting. If false, nothing happens. Since B2 is already seven days after the date in A2, any date past that will result in the statement being true.

I highlighted the cell with a red fill and white font. You can, of course, use any formatting you choose.

Once you have this Conditional Formatting formula entered into B2, you can use the Format Painter to copy the conditional formatting to other cells.

I hope this helps.