Date Calculation & Conditional Formatting
 

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

Date Calculation & Conditional Formatting
 

1. Select the cell G2
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula

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

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"Stumped" wrote:

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

Date Calculation & Conditional Formatting
 

Thanks for the quick and correct answer. I do have a question for you,
however. Inyour formula you have cell G20......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?

"Jacob Skaria" wrote:

1. Select the cell G2
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula

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

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"Stumped" wrote:

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

Date Calculation & Conditional Formatting
 

you have cell G20......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
G20 takes fewer keystrokes!


--
Biff
Microsoft Excel MVP


"Stumped" wrote in message
...
Thanks for the quick and correct answer. I do have a question for you,
however. Inyour formula you have cell G20......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?

"Jacob Skaria" wrote:

1. Select the cell G2
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula

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

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"Stumped" wrote:

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