Conditional formatting with date in different cell

[Midri]

Goodday,

I have a small issue which I can't seem to find the solution to, although I'm probably overlooking it again.

I want to get some dates highlighted in a dynamic table. I know of conditional formatting and it's functions, but I don't seem to find the right formula to use.

The situation:
The dates are automatically entered per row, starting from row F4 to T4. The next entry will be on row F5 to T5 etc.
In row F2 to T2 I have the different renewal dates in years (1 year, 2 years etc).
So for an example: The date in F4 says: 15/01/2015. The date renewal in F2 says: 1 year. So I want the conditional formatting start it's work (making the cell red) when the date (on the computer) passes 15/01/2016.

Another example:
Date in G4 says: 15/01/2015. The date renewal in G2 says: 5 years. So I want the cel getting red when the date (on the computer) passes 15/01/2020.


Thanks a lot in advance to anyone that can help me!

[sktneer]

Assuming you want to apply conditional formatting to the range F4:T25, having renewal periods in row 2 in the range F2:T2, then Select the range F4:T25 and make a New Rule for conditional formatting using the formula given below and set the formatting as per your choice.

Please Login or Register  to view this content.

[tchock]

new rule based on formula to apply in conditional formatting:

=DATE(YEAR(F2)+G2,MONTH(F2),DAY(F2))<TODAY()