Formatting Issues

How to Assign Colors to Recurring Weekdays in Excel

Guest Writer Sep 27, 2026 869 views

Question details

The user needs to automatically color-code cells containing dates based on the specific day of the week, while preventing empty cells from being formatted.

How to Automatically Assign Colors to Recurring Weekdays in Excel

Product: Excel
Device & OS: not provided
Scenario: Formatting a calendar, schedule, or timesheet where different days of the week require distinct visual indicators for better readability.
Observed behavior: By setting up conditional formatting rules that combine the WEEKDAY function with a non-blank check, date cells are dynamically highlighted based on the weekday while ignoring empty cells.

Before you start

Ensure your target cells are properly formatted as dates in Excel, and identify the exact cell range you wish to color-code before creating your rules.

Solution 1Recommended

Use Conditional Formatting with the WEEKDAY Function

Create specific formula-based conditional formatting rules for each day of the week to automatically assign colors without affecting blank cells.

To reliably color-code recurring weekdays, you should avoid manual formatting or dragging the fill handle. Instead, use a formula-based conditional formatting rule combining the AND and WEEKDAY functions. This ensures accurate formatting even when dates change.

Select your date range

Highlight the range of cells containing the dates you want to format (e.g., A1:A100).

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the WEEKDAY formula

In the formula box, enter =AND($A1<>"",WEEKDAY($A1,2)=7) to format Sundays. In this formula, the '2' sets Monday as day 1 and Sunday as day 7. The $A1<>"" part ensures blank cells are skipped.

Set the cell color

Click the 'Format' button, go to the 'Fill' tab, choose your desired color for Sunday, and click 'OK' twice to apply the rule.

Repeat for other days

Create a new rule for each additional weekday by repeating these steps and changing the '7' at the end of the formula to the corresponding day number (e.g., 1 for Monday, 6 for Saturday).

Use Conditional Formatting with the WEEKDAY Function

Efficient Rule Management: You can manage and review all your weekday rules by going to Home > Conditional Formatting > Manage Rules. Make sure the 'Applies to' range is correct for all your rules.

Smart Data Formatting with WPS Office

Color-Code Your Schedules Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, allowing you to highlight weekdays effortlessly. It is an excellent, highly compatible tool for managing complex data formatting.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your date records.
2. Navigate to Conditional Formatting: Select your data range, go to the Home tab, click on 'Conditional Formatting', and choose 'New Rule'.
3. Apply the WEEKDAY formula: Select 'Use a formula to format cells', input your formula such as =AND($A1<>"",WEEKDAY($A1,2)=1) for Monday, click 'Format' to pick your color, and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting rulesSupports advanced logical formulas like WEEKDAY and ANDLightweight software with a familiar, user-friendly interfaceFree to download and seamless migration of existing Excel files

microsoft office alternative - wps office

Frequently Asked Questions

Why do my blank cells turn purple or another color when I apply the weekday formula?

This happens because Excel treats blank cells as a zero value, which corresponds to a Saturday (day 6) in the default WEEKDAY function. To fix this, you must include a non-blank condition using the AND function, such as =AND($A1<>"",WEEKDAY($A1,2)=7).

How do I change the starting day of the week for the WEEKDAY function?

The second argument in the WEEKDAY function determines the start day. Using '2' (e.g., WEEKDAY($A1,2)) sets Monday as day 1 and Sunday as day 7. Using '1' or omitting the argument sets Sunday as day 1 and Saturday as day 7.

Can I highlight the entire row based on the date's weekday?

Yes. Select the entire range of rows you want to format (e.g., $A$1:$F$100), and ensure the column reference for your date in the formula is absolute (e.g., $A1 instead of A1). This forces the rule to evaluate the date in column A to format the entire row.

Will this formatting update automatically if I change the date?

Yes, conditional formatting is dynamic. If you change a date in the formatted cell, the WEEKDAY formula will recalculate immediately, and the cell color will automatically update to match the new day of the week.

Document & File Issues