Conditional Formatting ?

[wisemank]

Hi there...
I have a training matrix... I want the format to change when the original training date exceeds 3 years relative to todays date.


Example:
A2 = Johnny Smith
B2 = 10/06/2012
C2 = 10/08/2015 (3yrs later + 2 days make me red!)

Column B is the starting dates for specific training topics
Column C is the forecasted renewal dates (original + 3years)

If data in C expires before current date (ie... todays date) the format...

Hope I am clear enough... its the issue with referencing today that has me messed up

thanks in advance

Kip

 

[Matt Rogers]

Hi.

The formula is:

=C2>DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)+1)

 

[Sense-A]

Okay here is what row 2 looks like:

[TABLE="width: 239"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnny Smith[/TD]
[TD="align: right"]10/6/2012[/TD]
[TD="align: right"]10/6/2015[/TD]
[/TR]
</tbody>[/TABLE]

Cell C2 formula is:

=DATE(YEAR(B2)+3,MONTH(B2),DAY(B2))

Select cell C2, click Conditional Formatting
Click New Rule
Enter this in the formula box:

=NOW()>C2

Click Format
Click Fill
Select Red color
OK
Should work
Try it

 

[Matt Rogers]

I thought column C is completed ...