Current year + 3 years

chimp

Board Regular
Joined
Nov 17, 2003
Messages
80
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.

please help

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:

http://www.ozgrid.com/forum/showthread.php?t=42127
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0

Forum statistics

Threads
1,226,618
Messages
6,192,046
Members
453,693
Latest member
maverick688

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top