Using Dates in Conditional Formatting
 

Im using conditional formatting to change the color of a cell to blue if the
date entered in that cell is less than todays date. The formula works fine
as long as theres any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting. I
tried to fix this by adding a second condition, but it still didnt work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

Delete condition 2 and modify condition 1 like this:

=AND(G30,G3<TODAY())

--
Biff
Microsoft Excel MVP


"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting.
I
tried to fix this by adding a second condition, but it still didn't work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

Hi Marie,

try something like

=AND(G3<TODAY();G30)

AFAIK, conditions are handled sequentially, so the second condition is
skipped when the first met the criteria.

Wkr,

JP
"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting.
I
tried to fix this by adding a second condition, but it still didn't work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

Put it all in Condition1

=AND(ISNUMBER(G3),G3<TODAY())

Delete Condition2


Gord Dibben MS Excel MVP


On Mon, 17 Aug 2009 09:15:02 -0700, Marie FP
wrote:

I’m using conditional formatting to change the color of a cell to blue if the
date entered in that cell is less than today’s date. The formula works fine
as long as there’s any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting. I
tried to fix this by adding a second condition, but it still didn’t work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

This worked! Thanks very much!!

"T. Valko" wrote:

Delete condition 2 and modify condition 1 like this:

=AND(G30,G3<TODAY())

--
Biff
Microsoft Excel MVP


"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting.
I
tried to fix this by adding a second condition, but it still didn't work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

JP, I think the semicolon after "TODAY()" was a typo, so I changed it to a
comma, and it worked great. Also, the tip about conditions working
sequentially is good to know (which I didn't). Thanks very much!!

"JP Ronse" wrote:

Hi Marie,

try something like

=AND(G3<TODAY();G30)

AFAIK, conditions are handled sequentially, so the second condition is
skipped when the first met the criteria.

Wkr,

JP
"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting.
I
tried to fix this by adding a second condition, but it still didn't work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

This (plus the other two suggestions I got) was a good one, too. Thank you!!

"Gord Dibben" wrote:

Put it all in Condition1

=AND(ISNUMBER(G3),G3<TODAY())

Delete Condition2


Gord Dibben MS Excel MVP


On Mon, 17 Aug 2009 09:15:02 -0700, Marie FP
wrote:

Im using conditional formatting to change the color of a cell to blue if the
date entered in that cell is less than todays date. The formula works fine
as long as theres any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting. I
tried to fix this by adding a second condition, but it still didnt work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

JP, I think the semicolon after "TODAY()" was a typo,

It depends on JP's locale. Some countries use a semi-colon as a list
separator where we in the US would use a comma.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 17 Aug 2009 11:55:01 -0700, Marie FP
wrote:

JP, I think the semicolon after "TODAY()" was a typo, so I changed it to a
comma, and it worked great. Also, the tip about conditions working
sequentially is good to know (which I didn't). Thanks very much!!

"JP Ronse" wrote:

Hi Marie,

try something like

=AND(G3<TODAY();G30)

AFAIK, conditions are handled sequentially, so the second condition is
skipped when the first met the criteria.

Wkr,

JP
"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting.
I
tried to fix this by adding a second condition, but it still didn't work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Marie FP" wrote in message
...
This worked! Thanks very much!!

"T. Valko" wrote:

Delete condition 2 and modify condition 1 like this:

=AND(G30,G3<TODAY())

--
Biff
Microsoft Excel MVP


"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue
if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell
is
empty, it is also turns blue, but I would like it to have no
formatting.
I
tried to fix this by adding a second condition, but it still didn't
work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color

Using Dates in Conditional Formatting
 

Aaahh, I didn't know that. Thanks for the info, Chip.


"Chip Pearson" wrote:

JP, I think the semicolon after "TODAY()" was a typo,

It depends on JP's locale. Some countries use a semi-colon as a list
separator where we in the US would use a comma.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 17 Aug 2009 11:55:01 -0700, Marie FP
wrote:

JP, I think the semicolon after "TODAY()" was a typo, so I changed it to a
comma, and it worked great. Also, the tip about conditions working
sequentially is good to know (which I didn't). Thanks very much!!

"JP Ronse" wrote:

Hi Marie,

try something like

=AND(G3<TODAY();G30)

AFAIK, conditions are handled sequentially, so the second condition is
skipped when the first met the criteria.

Wkr,

JP
"Marie FP" wrote in message
...
I'm using conditional formatting to change the color of a cell to blue if
the
date entered in that cell is less than today's date. The formula works
fine
as long as there's any date in the cell. The problem is when the cell is
empty, it is also turns blue, but I would like it to have no formatting.
I
tried to fix this by adding a second condition, but it still didn't work.
Does anyone know how I can fix this? My conditions look like this:

Condition 1: Forumla Is =G3<TODAY() --- color is blue.
Condition 2: Cell Value Is equal to 0 --- color is no color