Excel Tip: How to highlight weekend dates in Excel using Conditional Formatting

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

Suppose you are working on a worksheet that contains a date column. How would you determine a weekend date from a weekday? Using Conditional Formatting, this can be done easily. The screen shot below are from MS Excel 2010.

How to highlight weekend dates in Excel using Conditional Formatting

Suppose you have the data as shown below, with column A as the Date column.

Sample date column in Excel

To distinguish the weekend dates from weekdays in the date column, you can use Conditional Formatting. Here are the steps:

Step 1: Highlight the cells in Column  A containing dates. In the sample file, it is A2:A25.

Step 2: In the Home tab, under the Styles group, click on Conditional Formatting then click on Manage Rules.

Manage rules command in Conditional Formatting

Step 3: Click on New Rules... button

Step 4: In the New Formatting Rule dialog box, select the bottom option "Use a formula to determine which cells to format".

Step 5: In the Formula box, enter the formula =WEEKDAY($A2,2)>5  which will check weekday in the range satrting from A2.

Step 6: Click on the Format button and set the Font Style to bold and color it red. Then click OK.

weekday formula for conditional formatting

Step 7: Click Apply and you shall see the weekend dates highlighted in red and displayed bold.

 

weekend dates highlighted through conditional formatting

 

MS Office Software: