Would like to create conditional formating that shades a cell one color if
the content of the cell is a date and another color of the content of the
cell is Text.
I suspect this problem is easily solved however I've managed to get my self
stuck for the last hour.
Any assistance would be appreciated

Something to consider about formatting for dates...

In Excel dates are stored as numbers but they display as a formatted date.
For example, today is 3/24/2009. That's what we humans see but to Excel
3/24/2009 is really the number 39896. To see this enter the date 3/24/2009
in cell A1. With A1 selected goto the menu FormatCellsNumber
tabGeneralOK.

Excel calculates the date as the number of days since a base date. That base
date (using the default date system) is January 1 1900. January 1 1900 is
serial date 1. January 2 1900 is serial date 2. 3/24/2009 is serial date
39896. The 39896th day since January 1 1900.

Ok, by now you might be wondering what this all has to do with your wanting
to format cells with dates. The standard method of testing a cell to see if
it has a date is to test it to see if it contains a number since dates are
just formatted numbers.

=ISNUMBER(A1)

This returns either TRUE or FALSE. So, if someone enters the date 3/24/2009,
ISNUMBER is TRUE and the format is applied. However, if someone enters the
number 99 in the cell this also evaluates to TRUE and the format will be
applied.

You may not have to account that but one way to do it is to set a range for
allowable date entries. For example, only format the cell if the date falls
within the date range 1/1/2009 to 8/4/2009. This will eliminate accidental
entries like 99 from being formatted and if the cell isn't formatted that
might draw your attention to a possible error.

OK, here's how to apply the formatting (finally!)...

Let's assume you want to format cell A1
Select cell A1
Goto the menu FormatConditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=ISNUMBER(A1)
Click the Format button
Select the Patterns tab
Select a color
OK

Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=ISTEXT(A1)
Click the Format button
Select the Patterns tab
Select a color
OK out

If you want to set a date range change the formula in condition 1 to
something like this:

=AND(A1=DATE(2009,1,1),A1<=DATE(2009,8,4))

--
Biff
Microsoft Excel MVP


"Ants71" wrote in message
...
Would like to create conditional formating that shades a cell one color if
the content of the cell is a date and another color of the content of the
cell is Text.
I suspect this problem is easily solved however I've managed to get my
self
stuck for the last hour.
Any assistance would be appreciated

Nice one - Good response.
--
Cheers
Ant.


"T. Valko" wrote:

Something to consider about formatting for dates...

In Excel dates are stored as numbers but they display as a formatted date.
For example, today is 3/24/2009. That's what we humans see but to Excel
3/24/2009 is really the number 39896. To see this enter the date 3/24/2009
in cell A1. With A1 selected goto the menu FormatCellsNumber
tabGeneralOK.

Excel calculates the date as the number of days since a base date. That base
date (using the default date system) is January 1 1900. January 1 1900 is
serial date 1. January 2 1900 is serial date 2. 3/24/2009 is serial date
39896. The 39896th day since January 1 1900.

Ok, by now you might be wondering what this all has to do with your wanting
to format cells with dates. The standard method of testing a cell to see if
it has a date is to test it to see if it contains a number since dates are
just formatted numbers.

=ISNUMBER(A1)

This returns either TRUE or FALSE. So, if someone enters the date 3/24/2009,
ISNUMBER is TRUE and the format is applied. However, if someone enters the
number 99 in the cell this also evaluates to TRUE and the format will be
applied.

You may not have to account that but one way to do it is to set a range for
allowable date entries. For example, only format the cell if the date falls
within the date range 1/1/2009 to 8/4/2009. This will eliminate accidental
entries like 99 from being formatted and if the cell isn't formatted that
might draw your attention to a possible error.

OK, here's how to apply the formatting (finally!)...

Let's assume you want to format cell A1
Select cell A1
Goto the menu FormatConditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=ISNUMBER(A1)
Click the Format button
Select the Patterns tab
Select a color
OK

Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=ISTEXT(A1)
Click the Format button
Select the Patterns tab
Select a color
OK out

If you want to set a date range change the formula in condition 1 to
something like this:

=AND(A1=DATE(2009,1,1),A1<=DATE(2009,8,4))

--
Biff
Microsoft Excel MVP


"Ants71" wrote in message
...
Would like to create conditional formating that shades a cell one color if
the content of the cell is a date and another color of the content of the
cell is Text.
I suspect this problem is easily solved however I've managed to get my
self
stuck for the last hour.
Any assistance would be appreciated

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Fats" wrote in message
...
Nice one - Good response.
--
Cheers
Ant.


"T. Valko" wrote:

Something to consider about formatting for dates...

In Excel dates are stored as numbers but they display as a formatted
date.
For example, today is 3/24/2009. That's what we humans see but to Excel
3/24/2009 is really the number 39896. To see this enter the date
3/24/2009
in cell A1. With A1 selected goto the menu FormatCellsNumber
tabGeneralOK.

Excel calculates the date as the number of days since a base date. That
base
date (using the default date system) is January 1 1900. January 1 1900 is
serial date 1. January 2 1900 is serial date 2. 3/24/2009 is serial date
39896. The 39896th day since January 1 1900.

Ok, by now you might be wondering what this all has to do with your
wanting
to format cells with dates. The standard method of testing a cell to see
if
it has a date is to test it to see if it contains a number since dates
are
just formatted numbers.

=ISNUMBER(A1)

This returns either TRUE or FALSE. So, if someone enters the date
3/24/2009,
ISNUMBER is TRUE and the format is applied. However, if someone enters
the
number 99 in the cell this also evaluates to TRUE and the format will be
applied.

You may not have to account that but one way to do it is to set a range
for
allowable date entries. For example, only format the cell if the date
falls
within the date range 1/1/2009 to 8/4/2009. This will eliminate
accidental
entries like 99 from being formatted and if the cell isn't formatted that
might draw your attention to a possible error.

OK, here's how to apply the formatting (finally!)...

Let's assume you want to format cell A1
Select cell A1
Goto the menu FormatConditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=ISNUMBER(A1)
Click the Format button
Select the Patterns tab
Select a color
OK

Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=ISTEXT(A1)
Click the Format button
Select the Patterns tab
Select a color
OK out

If you want to set a date range change the formula in condition 1 to
something like this:

=AND(A1=DATE(2009,1,1),A1<=DATE(2009,8,4))

--
Biff
Microsoft Excel MVP


"Ants71" wrote in message
...
Would like to create conditional formating that shades a cell one color
if
the content of the cell is a date and another color of the content of
the
cell is Text.
I suspect this problem is easily solved however I've managed to get my
self
stuck for the last hour.
Any assistance would be appreciated