Set Excel Conditional Formatting Thresholds for Weekday and Weekend Shift Counts
Question details
Create conditional formatting rules that highlight daily shift counts when weekday and weekend staffing thresholds are exceeded.

- Product
- Microsoft Excel conditional formatting
- Device & OS
- not provided
- Scenario
- The worksheet has a date for each shift and a corresponding daily staffing or shift-count value. Sunday through Thursday use one threshold, while Friday and Saturday use a higher threshold.
- Observed behavior
- The user needs count cells to receive different conditional formatting based on both the date's day of week and the applicable staffing threshold.
Identify the column containing the shift date and the column containing the daily count. In the examples below, the date is in column A and the count to format is in column B; adjust the references to match your sheet.
Create separate formula rules for Sunday–Thursday and Friday–Saturday
Use two formula-based conditional formatting rules when the threshold depends on the day represented by each row's date.
Use WEEKDAY with return type 1 so Sunday is 1, Thursday is 5, Friday is 6, and Saturday is 7. Keep the date-column reference fixed with a dollar sign and leave the row number relative so each formatted row checks its own date and count.
Select the cells that contain the daily counts, such as B2:B100. Do not select the date column unless you also want the date cells to be highlighted.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND($A2<>"",WEEKDAY($A2,1)<=5,$B2>6), then choose the fill color to use when the Sunday–Thursday threshold is exceeded.
With the same count range selected, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND($A2<>"",WEEKDAY($A2,1)>=6,$B2>8), then choose the weekend fill color.
Open Home > Conditional Formatting > Manage Rules and confirm that both rules apply to the count range, such as =$B$2:$B$100. Replace 6 and 8 with your actual weekday and weekend staffing limits.

Apply Day-Based Staffing Thresholds in WPS Spreadsheets
WPS Spreadsheets lets you create formula-based conditional formatting rules for daily shift counts. Use WEEKDAY to test each row's date, then apply a different color when the weekday or weekend count exceeds its assigned limit.
- 1. Open the staffing worksheet: Open the workbook in WPS Spreadsheets and confirm which column contains dates and which column contains daily counts.
- 2. Select the count cells: Select the target count range, such as B2:B100, then choose Home > Conditional Formatting > New Rule.
- 3. Create the weekday threshold rule: Choose a formula-based rule and enter =AND($A2<>"",WEEKDAY($A2,1)<=5,$B2>6). Select the desired formatting color.
- 4. Create the weekend threshold rule: Create another formula rule with =AND($A2<>"",WEEKDAY($A2,1)>=6,$B2>8), then select its formatting color.
- 5. Review and save the rules: Open Conditional Formatting > Manage Rules to check the applied range and save the workbook in .xlsx format if it will be shared with Excel users.

Frequently Asked Questions
Why does WEEKDAY with return type 1 work for Sunday through Thursday?
WEEKDAY(date,1) returns Sunday as 1 through Saturday as 7. Therefore, <=5 covers Sunday, Monday, Tuesday, Wednesday, and Thursday, while >=6 covers Friday and Saturday.
How do I highlight the entire row instead of only the shift count cell?
Select the full row range to format, such as A2:F100, and keep the date and count references locked to their columns in the formulas: $A2 for the date and $B2 for the count.
Can weekday and weekend rules use different colors?
Yes. In each Conditional Formatting rule, choose a different format in the Format or formatting-style options. For example, use green for weekday counts above 6 and orange for weekend counts above 8.
What should I change if Friday and Saturday have a different threshold value?
Edit the number at the end of the Friday–Saturday formula. For example, change $B2>8 to $B2>10 if counts above 10 should be highlighted on Friday and Saturday.




