Solved I am trying to do a if statment in excel that has a date

October 9, 2013 at 07:42:05
Specs: Windows Pr
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

See More: I am trying to do a if statment in excel that has a date

Report •


✔ Best Answer
October 9, 2013 at 10:03:02
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/13

In 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

http://www.skeptic.com/



#1
October 9, 2013 at 08:17:29
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 OK

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

MIKE

http://www.skeptic.com/


Report •

#2
October 9, 2013 at 08:55:54
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/13

04/06/13


14/06/13


Report •

#3
October 9, 2013 at 09:28:10
I'm sorry, I don't understand your last post.
Is there a question in there somewhere that I'm missing?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 9, 2013 at 10:03:02
✔ 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/13

In 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

http://www.skeptic.com/


Report •

Ask Question