Your conditional formatting rule to highlight weekend dates is not working. The cells remain unformatted even when the date falls on a Saturday or Sunday. This happens because the WEEKDAY function returns a number, and the conditional formatting logic must match it correctly. This article explains the common formula errors and provides the correct steps to fix your weekend highlighting.
Key Takeaways: Fix Weekend Highlighting in Excel
- WEEKDAY(date,2) > 5: This formula correctly identifies Saturday (6) and Sunday (7) when using the return_type argument of 2.
- Conditional Formatting > New Rule > Use a formula: You must apply the corrected WEEKDAY formula here for the rule to evaluate each cell.
- Manage Rules > Edit Rule: Use this dialog to check and correct the formula and its applied range if the formatting is not showing.
Why Your WEEKDAY Formula Fails to Highlight Weekends
The WEEKDAY function in Excel returns a number from 1 to 7 representing the day of the week for a given date. The most common failure is using the wrong return_type argument. The default behavior, WEEKDAY(date), treats Sunday as day 1 and Saturday as day 7. A formula like =WEEKDAY(A1)>5 would then only highlight Saturday, missing Sunday entirely.
Another frequent error is an incorrect cell reference in the conditional formatting formula. If you use an absolute reference like =WEEKDAY($A$1)>5, the rule only checks the date in cell A1 for the entire selected range. The rule will not evaluate the date in each individual cell. The formula logic must be relative to the top-left cell of the applied range.
How Excel Interprets the Return_Type Argument
The second argument in the WEEKDAY function defines the numbering system. For highlighting weekends, the system where Monday is 1 and Sunday is 7 is most reliable. You specify this by using 2 as the return_type: WEEKDAY(date, 2). In this system, Saturday is 6 and Sunday is 7, so the condition WEEKDAY(date, 2) > 5 correctly captures both days.
Steps to Create a Correct Weekend Highlighting Rule
Follow these steps to build a conditional formatting rule that reliably highlights Saturdays and Sundays.
- Select your date range
Click and drag to select the cells containing the dates you want to format. For example, select range A2:A100. - Open the Conditional Formatting menu
Go to the Home tab on the ribbon. In the Styles group, click Conditional Formatting. From the dropdown menu, select New Rule. - Choose the formula rule type
In the New Formatting Rule dialog box, select the rule type "Use a formula to determine which cells to format." - Enter the corrected WEEKDAY formula
In the formula input box, type =WEEKDAY(A2,2)>5. If your top-left selected cell is not A2, replace "A2" with its address. Do not use dollar signs before the row number, like A$2, to allow the rule to adjust for each row. - Set the highlight format
Click the Format button. In the Format Cells dialog, go to the Fill tab and choose a highlight color, like light gray or yellow. Click OK to close the Format Cells dialog. - Apply and verify the rule
Click OK in the New Formatting Rule dialog. Your selected range should now show the chosen color for any date that is a Saturday or Sunday.
If Your Conditional Formatting Still Does Not Work
Rule Applied but No Cells Are Highlighted
First, verify your cells contain real Excel dates, not text that looks like a date. Select a cell and check the Number Format in the Home tab. If it says "Text," the WEEKDAY function will return an error. Convert text to dates by selecting the column, going to Data > Text to Columns, and clicking Finish in the wizard.
Only Some Weekend Dates Are Highlighted
This indicates a problem with the formula’s relative reference. Go to Home > Conditional Formatting > Manage Rules. Select your rule and click Edit Rule. Ensure the formula references the active cell of the "Applies to" range correctly, without absolute column locking (e.g., use A2, not $A2).
Formatting Appears on Wrong Days
The return_type argument in your WEEKDAY formula is wrong. Edit the rule and change the formula. Use =OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7) to highlight Sunday and Saturday in the default system. For the Monday=1 system, use =WEEKDAY(A2,2)>5 as shown in the main steps.
WEEKDAY Formula Methods for Highlighting Weekends
| Item | Formula: =WEEKDAY(date,2)>5 | Formula: =OR(WEEKDAY(date)=1,WEEKDAY(date)=7) |
|---|---|---|
| Return Type System | Monday = 1, Sunday = 7 | Sunday = 1, Saturday = 7 |
| Weekends Identified | Saturday (6) and Sunday (7) | Sunday (1) and Saturday (7) |
| Best For | Standard workweek views where Monday is the first day | Calendars or regions where the week starts on Sunday |
| Formula Length | Shorter and simpler | Longer, uses the OR function |
You can now reliably highlight weekend dates in your Excel sheets. Use the WEEKDAY function with return_type 2 for a clean formula. Check the Manage Rules dialog if the formatting behaves unexpectedly. For more control, try using the WORKDAY.INTL function to create a rule that highlights custom non-working days beyond just weekends.