I am trying to do a if statement in excel that has a date, it should flag up red if the date is greater then 3 years

✔ Best Answer

Ok, I think I understand what your looking for.

If your looking to add three years to your existing dates,

and have them turn colors on the approach of that date, then try this:With your data like:

A 1) Breakaway Training 2) 03/05/2013 3) 14/06/13 4) 03/05/2013 5) 04/06/2013 6) 04/06/2013 7) 14/06/13In cell B2 enter the formula: =DATE(YEAR(A2)+3,MONTH(A2),DAY(A2))

Drag down as many rows as needed.

This will give you a date, in column B, three years from the Date in column A.

If you then want cells to be highlighted in advance of the three year deadline

then see this How-To:http://www.computing.net/howtos/sho...

MIKE

You will need to use Conditional Formatting to get it Red. Try this:

With your three year old date in cell A1:

Conditional Formatting 2007

1) Select your cell A1

2) On the ribbon click Conditional Formatting

3) Click on New Rules, itâ€™s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula:=DATEDIF(A1,TODAY(),"Y")>=3

6) Click on the Format button

7) Select the Fill Tab

8) Select a RED color

9) Click OK

10) Click OKIf your date in cell A1 is equal to or greater than 3 years old, it should turn Red.

MIKE

Thanks Mike for your repose it greatly appreciated. Just on the back of that this is a extract from my spreadsheet. The date below shows the last time a member of staff had training so i can use that formula to work out which members of staff are due training after the 3 years and it will flag this up in red Breakaway Training (3 Yearly)

03/05/13

14/06/13

03/05/13

04/06/1304/06/13

14/06/13

I'm sorry, I don't understand your last post.

Is there a question in there somewhere that I'm missing?MIKE

Ok, I think I understand what your looking for.

If your looking to add three years to your existing dates,

and have them turn colors on the approach of that date, then try this:With your data like:

A 1) Breakaway Training 2) 03/05/2013 3) 14/06/13 4) 03/05/2013 5) 04/06/2013 6) 04/06/2013 7) 14/06/13In cell B2 enter the formula: =DATE(YEAR(A2)+3,MONTH(A2),DAY(A2))

Drag down as many rows as needed.

This will give you a date, in column B, three years from the Date in column A.

If you then want cells to be highlighted in advance of the three year deadline

then see this How-To:http://www.computing.net/howtos/sho...

MIKE

Ask Your Question

Weekly Poll