Cherisse asks:

Is there a way to set a conditional format for a cell or row to look at the date and format if that date falls on a specific day of the week. I.e. a date input as 7/21/09, cell formatted to show "Tuesday, July 21, 2009", want to format all cells that contain the word "Tuesday" as red.

I know from experience that many users seem unaware that dates in Excel are actually numbers. It drives me crazy, as it can easily bugger up formulas later on down the line, but fortunately we can deal with this.

So Cherisse, the answer is... Of course! One thing I would advise though... if you can, prevent the user from entering an invalid date to begin with using Data Validation first, then you don't have to worry about the garbage ever getting in to your spreadsheet. But, because you asked, I'll walk through the conditional format route first...

Re-Active Date Validation Using Conditional Formats

To validate this data, I would go through two steps.

The first step is to set up the format on the cell for the correct input type. So right click the cell, choose "Format Cells". On the Number Format tab, choose Date, and choose the date format:

Second, we set up a conditional format. In Excel 2003 go to Format --> Conditional Formatting  -->  Formula Is. In Excel 2007 go to Home --> Conditional Formatting --> New Rule --> Use a Formula...

Set up the rule as follows (assuming the input cell is in B5):

• Formula:     =IsNumber(B5)=False
• Font:          Bold, White
• Fill:            Red

This will highlight the cell in red if the input is not numeric, as shown below. (I copied the format to cell B6 as well.)

Okay, so this is great, but what if we want to change this so that anything that isn't a valid Tuesday is highlighted? We do this by modifying the conditional format's formula:

• Excel 2003    =OR(ISNUMBER(B5)=FALSE,IF(ISERR(WEEKDAY(B5)<>3),TRUE,WEEKDAY(B5)<>3))
• Excel 2007+  =OR(ISNUMBER(B5)=FALSE,IFERROR(WEEKDAY(B5)<>3,TRUE))

Pro-Active Date Validation Using Data Validation

As I mentioned above, I'd actually approach this a little differently, and prevent a user from even entering invalid data in the first place. The method to do that is Excel's Data Validation tool.

In Excel 2003 go to Data --> Validation. In Excel 2007 go to Data --> Data Validation

I usually set up the rule as follows:

• Allow:           Date
• Data:            Greater Than
• Start Date:    =Today()-1

This rule means that you can enter today's date or higher.

We want only want the user to enter a valid Tuesday though, so let's change this a bit and go with the following rule:

• Allow:         Custom
• Formula:    =WEEKDAY(B5)=3

The real slick thing here is that you don't need a conditional format, as the user will be prevented from ever entering anything that is not a valid date AND a Tuesday.

One word of advice though... the default error message isn't really intuitive:

You can customize this error by filling in the Error Alert tab. I usually put a nice descriptive error in there to tell the user what to do. Something like:

This field only accepts Tuesday's that are entered in a valid format. Please enter a Tuesday in the format MM/DD/YY.

The effect will look like this:

And if you want to be even more helpful, fill in the Input Message tab as well...

Conclusion

Wherever possible, you should try pro-actively validate your data before the user can even commit it. The Data Validation tool is great for this purpose. You will find, however, that there will be times where you just can't pro-actively evaluate everything... and that is where conditional formatting can be really handy as a Re-Active Data Validation tool. I often use the two in combination to generate a really robust interface.