Formatting Issues

How to Change Nonworking Days in Excel Attendance Calendar on Mac

Maira Mehtab Sep 28, 2026 868 views

Question details

The user wants to modify an Excel attendance calendar template to automatically mark Wednesdays and Saturdays as nonworking or closed days.

Product: Excel
Device & OS: Mac
Scenario: Customizing a monthly attendance calendar template to accurately reflect specific non-standard weekly days off.
Observed behavior: The user needs a method to dynamically shade specific days (Wednesdays and Saturdays) as closed days within the calendar grid without manually editing the base template structure.

Before you start

Ensure your calendar has a defined start date cell (e.g., cell A1) and identify the specific range of cells that make up your calendar grid (e.g., A3:G8) before applying formatting rules.

Solution 1Recommended

Use Conditional Formatting to Highlight Custom Nonworking Days

Apply a custom conditional formatting formula to automatically shade Wednesdays and Saturdays based on the calendar's starting date.

Select the calendar range

Click and drag your mouse to highlight the specific cells that contain your calendar dates, such as A3:G8.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the drop-down menu.

Set the formula rule

Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter `=OR(AND(MONTH(A3)=MONTH($A$1),WEEKDAY(A3)=4),AND(MONTH(A3)=MONTH($A$1),WEEKDAY(A3)=7))`.

Apply a fill color

Click the Format button, navigate to the Fill tab, choose a background color (like gray) to signify closed days, and click OK twice to apply the rule.

Adjusting Weekdays: The WEEKDAY function defaults to Sunday as 1, Wednesday as 4, and Saturday as 7. If your calendar uses a different starting day system, you may need to adjust the return type or the target numbers in the WEEKDAY arguments.

Effortless Spreadsheet Formatting

Customize Attendance Calendars Easily in WPS Spreadsheet

WPS Spreadsheet provides robust conditional formatting tools identical to Microsoft Excel, allowing you to easily manage customized attendance trackers, schedules, and calendars across Mac, Windows, and mobile devices.

1. Open your calendar: Launch WPS Spreadsheet and open your attendance calendar file.
2. Highlight the grid: Select the range of cells that display the calendar dates (e.g., A3:G8).
3. Access conditional formatting: Navigate to the Home tab on the ribbon, click Conditional Formatting, and select New Rule.
4. Apply the WEEKDAY formula: Choose the formula option, enter your specific WEEKDAY formula for Wednesdays and Saturdays, select a fill color, and click OK.

Fully compatible with Microsoft Excel (.xlsx) formulas and conditional formatting rules.Intuitive interface for managing complex date and time functions.Hundreds of free built-in calendar and attendance templates available.Cross-platform support including a highly optimized Mac version.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong days?

This typically occurs if the relative cell referenced in your formula (like A3) does not match the active top-left cell of your selected range. Ensure the relative reference in your formula exactly matches the first cell in your highlighted calendar grid.

How do I change the formula for different days off?

You can adjust the target numbers in the WEEKDAY function. Sunday is 1, Monday is 2, Tuesday is 3, Wednesday is 4, Thursday is 5, Friday is 6, and Saturday is 7. Simply replace the 4 and 7 in the provided formula with your desired nonworking days.

What if my attendance calendar does not have a month start date cell?

The formula requires a reference date to calculate the month correctly. You can insert a new row or use an existing blank cell to add a start date (e.g., the 1st of the month). You can hide this cell if needed, and then update the absolute reference ($A$1) in the formula to point to it.

Document & File Issues