How to Change the Color of Weekends with Conditional Formatting in 5 Simple Steps

1. Create a test for the weekend days: Use the WEEKDAY function to check if a day falls on a weekend.
2. Open Conditional Formatting: Go to Conditional Formatting > New Rule in the menu.
3. Select a custom formula: In the dialog, choose Use a formula to determine which cell to format.
4. Enter the formula: Type =WEEKDAY(B1, 2)>5 to highlight weekends (Saturday and Sunday).
5. Choose a color for weekends: Click Format, select Fill, pick a color, and confirm with OK.

Automatic Calendar

Here is a calendar showing how to color weekends. Each day in row 1 uses the DATE function to create a date according to the date in A1.

Step 1: Set Up the WEEKDAY Test

Use WEEKDAY function to identify weekends (1 for Sunday, 2 for Monday).

Step 2: Add Conditional Formatting Rule

In Conditional Formatting, select Use a formula to determine which cell to format.

Step 3: Apply the Formula

Enter =WEEKDAY(B$1, 2)>5 in the text box to highlight weekends.

Step 4: Set Your Custom Color

Then, to change the weekend’s color, you must set a color only with the test of the formula that will return TRUE.

1. Click Format and choose a color for weekends.
2. Go to Fill tab
3. Pick a color for the weekends and click OK.

Step 5: Apply to a Range of Cells

In Conditional Formatting> Manage Rules, extend the rule to cover the whole range.

Change the Color of Public Holidays

Highlight public holidays with the COUNTIF function, as explained here.

• Highlight Date Outside the Deadline
• Calculate Public holidays with Excel 📆
• Identifying Weekdays and Weekends with Excel
• Use 2 Different Highlight Colors in Excel
• Automatically Highlight Birthdays
• How to Highlight Min and Max values in Excel