Date Calculation & Conditional Formatting

[Stumped]

Hi, I am trying to use conditional formatting to color/highlight a cell but
am stumped.

Cell G2 has a date: 18 Jun 08
I need the cell to be colored/highlighted when 1 year has passed. SO, when
the spreadsheet is opened today on 7 Aug 09, the conditional formatting sees
that more than 1 year has passed since the date in cell G2 so teh cell is
highlighted.

How can I do this?

Thanks

 

[Jacob Skaria]

1. Select the cell G2
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula

=AND(G2>0,DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))<TODAY())

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes

 

[Stumped]

Thanks for the quick and correct answer. I do have a question for you,
however. Inyour formula you have cell G2>0......is that just to ensure cell
G2 has a value in it? If so, could it also be something like G2 is not null
or whatever that would be?

 

[T. Valko]

you have cell G2>0......is that just to ensure

cell G2 has a value in it?

Yes. If the cell is empty it will evaluate to 0 and in Excel 0 is less than
today so the cell would be highlighted which you don't want.

could it also be something like G2 is not null
or whatever that would be?

Yes. You could also use G2<>"" which means: G2 is not equal to blank. But
G2>0 takes fewer keystrokes!