Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.

In the next picture, a monthly calendar of daily events is listed, with Fridays being conditionally formatted in yellow. The Conditional Formatting formula rule that produces this result is
=WEEKDAY($A3)=6.
Notice that the non-date cells in column B are also highlighted in yellow, by virtue of the absolute reference to column A in the formula.

To start, show the Conditional Formatting dialog box by pressing Alt+O+D.

Versions 2003 and before, Step 1 of 3
• Select “Formula is” from the drop down list.
• Enter your conditional formula as shown.
• Click the Format button.

Versions 2003 and before, Step 2 of 3
In the Format Cells dialog box:
• Click the Patterns tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.

Versions 2003 and before, Step 3 of 3
You return to the Conditional Formatting dialog box, where you click the OK button to confirm.

Versions 2007 and after, Step 1 of 5
In the Conditional Formatting Rules Manager dialog box, click New Rule.

Versions 2007 and after, Step 2 of 5
In the New Formatting Rule dialog box, click the option “Use a formula to determine which cells to format”. Then, enter your formula rule as shown, and click the Format button.

Versions 2007 and after, Step 3 of 5
In the Format Cells dialog box:
• Click onto the Fill tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.

Versions 2007 and after, Step 4 of 5
You return to the New Formatting Rule dialog box, where you click the OK button to confirm.

Versions 2007 and after, Step 5 of 5
You return to the Conditional Formatting Rules Manager dialog box, where you click the OK button to confirm all your choices.