Conditional Formatting:Date

R

rmcgal

In Excel(2003)...how do you set up conditional formatting to change th
background color when a date is entered into the cell. I don't need t
format it to any particular range of dates, I simply want the color t
change to green when I enter a completion date in the cell. Other tha
entering a date, the cell would only contain text abbreviations fo
certain actions.

I have tried several different formulas and cannot get any to work.

It seems so basic...that is probably why it is stumping me.
:confused:

Thanks
 
S

swatsp0p

First, you need to understand that to Excel a Date is just a number that
it interprets as a date and, if formatted as such, displays that number
in a Date/Time format.

If you enter the number 1 in a cell and format as date, 1/1/1900 will
be displayed.

Therefore, you are asking for Conditional Formatting to respond to a
Format. It cannot do that.

I see two options:

1] generate some VBA code to read the contents of the cell and the
formatting of the cell and alter the fill color if it is a date.

2] If, as you say, the cell will either be blank, have a text entry or
a date, use CF to test which it is and format accordingly, as such:

Format the cell (A1 in this example) as Date with your default fill
color (no color)

Set CF Condition 1 as:

Formula Is: =$A$1*1>0 and choose your desired format for a date
entry (Pattern fill color=Green) Click OK, OK.

Now, a text entry in this cell will do nothing, any number entry will
change the fill color to Green.

HTH

Bruce
 
R

rmcgal

Thanks for the tip Bruce.

I went with option 2. That works great, since the only numeric entries
in those cells will be dates.

Have a great day! :)
 
S

swatsp0p

Glad it worked for you. Thanks for the feedback, it is alway
appreciated.

Cheers!

Bruc
 
Top