Checking for Thursday Transaction Dates

Kevin has transaction data for his company, and column G contains the date for each transaction. He needs to highlight transactions for which the date does not occur on a Thursday. (All of their transactions are to occur on Thursdays.) Kevin suspects this can be done with a conditional formatting rule, but he's not sure how to put the rule together.

You can set the proper conditional formatting rule by relying on the WEEKDAY function, which returns a number that represents the day of the week for a date. Follow these steps:

1. Select column G. Assuming you select the entire column, then cell G1 should also be active.
2. Make sure the Home tab of the ribbon is displayed.
3. Click the Conditional Formatting tool. Excel displays a series of choices.
4. Click New Rule. Excel displays the New Formatting Rule dialog box. (See Figure 1.)



Figure 1. The New Formatting Rule dialog box.

5. In the Select a Rule Type area at the top of the dialog box, choose Use a Formula to Determine Which Cells to Format. (See Figure 2.)



Figure 2. Use a Formula to Determine Which Cells to Format.

6. In the formula space, enter the following formula: =WEEKDAY(G1)<>5
7. Click the Format button. Excel displays the Format Cells dialog box. (See Figure 3.)



Figure 3. The Format Cells dialog box.

8. Using the controls in the dialog box, specify how you want the cells containing non-Thursday dates to be displayed.
9. Click OK to dismiss the Format Cells dialog box.
10. Click OK to accept your conditional format.

Excel immediately modifies the format of non-Thursday cells in column G to reflect your choices in step 8.

This is the simplest formula you can use in the conditional formatting rule, but there is a problem with it. If a cell in column G is empty, then it is formatted as "not a Thursday." If this bugs you as much as it would bug me, then you need to change the formula used in step 6 to one of these:

=AND(G1<>"",WEEKDAY(G1)<>5)
=AND(ISNUMBER(G1),WEEKDAY(G1)<>5)
=AND(NOT(ISBLANK(G1)),WEEKDAY(G1)<>5)

These all have the same result, so you should choose the one that makes the most sense to you. The AND function means, in this case, that two conditions need to be checked. The first condition is that the cell is not empty and the second is that the date in the cell is not a Thursday.

The formulas presented so far all use the WEEKDAY function to determine a numeric value for the day of the week. By default, WEEKDAY starts numbering days with Sunday, which means Thursday has a value of 5. You can use a second parameter with WEEKDAY, if you prefer, that starts the numbering with Thursday. Just replace the second check as illustrated here:

=AND(G1<>"",WEEKDAY(G1,14)>1)

There is one additional potential gotcha that you need to be aware of. Dates are stored internally as serial numbers. These numbers indicate the number of elapsed days since January 1, 1900. That day has a serial number of 1, the next day 2, the day after that 3, and so on. Because dates are numbers, WEEKDAY will return a weekday number for any numeric value in column G.

For instance, if you use the default format of WEEKDAY, then a value of 5 in a cell will be considered a Thursday. It doesn't matter if the cell is formatted as a date or not; WEEKDAY does this with any numeric value.

What does this mean in Kevin's situation? Just that if column G contains numbers other than dates, the conditional formatting rule may return results that you may find confusing. Like I said, it is a potential gotcha that you should be aware of. The cleanest way to get around the issue is to make sure that column G only contains three types of data: dates, text, or blanks.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13997) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.