How to Gray Out Non-Working Days in an Excel Attendance Calendar with WPS Office
Question details
The user wants to automatically gray out non-working days for a part-time employee in an attendance calendar by comparing calendar dates or weekday headers with each employee's working-day list.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing an attendance calendar for part-time employees and wants the calendar to reflect each employee's working-day schedule when that employee is selected from a drop-down list.
- Observed behavior
- Calendar days should be visually disabled with a gray fill when the selected employee is not scheduled to work.
Make sure the workbook contains a clear employee name list and a corresponding working-day list, and identify the exact employee drop-down cell and calendar header reference you will use in the formula.
Use a COUNTIFS conditional formatting rule to gray out non-working days
Use this method when the calendar has an employee drop-down selector and the employee list already contains each worker's scheduled working days.
This rule compares each calendar column or row header with the selected employee's working-day list. If no matching working-day entry exists, COUNTIFS returns 0 and the rule applies the gray fill.
In the sheet that stores employees, create or confirm one range for employee names, such as EmployeeNameRange, and another range for the working days or weekday headers assigned to each employee, such as WorkingDayRange.
Select the cells in the attendance calendar that you want to gray out, starting from the active cell that will be used in the formula, such as B4.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
Select 'Use a formula to determine which cells to format' and enter a formula like =COUNTIFS(EmployeeNameRange,$C$2,WorkingDayRange,B$4)=0, replacing the ranges and cell references with your actual layout.
Click Format, open the Fill tab, choose a gray color, and confirm the rule. Test by selecting a different employee from the drop-down list.
Gray Out Non-Working Days in WPS Office Spreadsheets
Use WPS Office Spreadsheets to build a COUNTIFS conditional formatting rule that compares the selected employee's working-day list and applies a gray fill to non-working calendar days.
- 1. Open the attendance calendar in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains your employee working-day list and calendar.
- 2. Arrange employee data: Ensure employee names and working-day ranges are stored in a structured table or named ranges so the formula can reference them correctly.
- 3. Apply the conditional formatting rule: Select the calendar grid, go to Home > Conditional Formatting > New Rule, and use a formula-based rule with COUNTIFS.
- 4. Set the gray fill and test: Choose gray formatting, confirm the rule, and change the employee drop-down to verify that non-working days update automatically.
Frequently Asked Questions
Can I gray out non-working days by weekday name instead of a full date?
Yes. If your calendar headers are weekday names or dates formatted as weekdays, adjust the COUNTIFS criteria to compare those header values with the employee's working-day list using the same mixed-reference style as your calendar layout.
Why is my COUNTIFS conditional formatting rule not highlighting any cells?
Check that the employee drop-down reference such as $C$2 is correct, the calendar header reference has the correct mixed reference such as B$4, and all ranges in COUNTIFS are the same size without extra blank rows.
Can I use this rule for multiple employees at the same time?
The formula works for one selected employee at a time from the drop-down. To handle multiple employees simultaneously, create separate rules or use a more advanced MATCH or SUMPRODUCT formula depending on the workbook layout.
Will this conditional formatting rule work in both WPS Office and Microsoft Excel?
Yes. COUNTIFS and formula-based conditional formatting are supported in WPS Office Spreadsheets, so the workbook remains compatible with Excel .xlsx files.
