# Current year + 3 years

#### chimp

##### Board Regular
I am trying to get a cell to display the refresh date for retesting....

Example

Test 1 ----- Test date 15/10/02 ------ Retest Date ---- 15/10/05

I have the first 2 bits and need a formula to calculate the additional 3 years for the retest date...this is now sorted

i then need this to be formatted to change colour from a green cell to red colour 3 months before the test date, so in this case on the 15/7/05 the cell would change to Red.

i assume this could be done via conditional formatting, but need some help please.

I can not get the conditional formatting to work as i want...

so to clarify if the date is greater than 3 months prior to the retest date is should turn red, if the retest date is more than 3 months away it should be green.

Andy
Training Matrix.xls
ABCDEFGHIJ
1
2NameDate of Last CourseType of TruckDate of Retest
3Chimp15-Jun-0415/06/2007
4Monkey15-Jun-0215/06/2005
5
6
Fork Lift

Also posted at:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Well i'll say how i would do it, but you'll probebly get a simpler or more efficent reply from someone else.

You say you get it to add the extra 3 years to it from your post. Did you do it with =DATE(YEAR(B3)+3,MONTH(B3),DAY(B3)

If so you can put a similar conditional format on D3 to say
Condition 1: "Cell Value is" - "Greater than" - "=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))" - then just set colour to GREEN

Condition 2: "Cell Value is" - "Less than or equal to:" - "=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))" - then just set colour to RED

EDIT: Typo

Replies
1
Views
388
Replies
3
Views
426
Replies
1
Views
228
Replies
6
Views
133
Replies
3
Views
292

1,203,103
Messages
6,053,541
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back