Conditional Formatting:Date

[rmcgal]

In Excel(2003)...how do you set up conditional formatting to change the background color when a date is entered into the cell. I don't need to format it to any particular range of dates, I simply want the color to change to green when I enter a completion date in the cell. Other than entering a date, the cell would only contain text abbreviations for 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.

Thanks!

[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

[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!

[swatsp0p]

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

Cheers!

Bruce