Formula Errors

How to Add Shifts and Fix Conditional Formatting Errors in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to add up to seven repeating shift patterns to a calendar using conditional formatting and resolve #NAME? formula errors.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a shift-work calendar that highlights different shift patterns with specific colors based on date and schedule formulas.
Observed behavior: The calendar returns #NAME? errors, indicating missing named ranges or unsupported formula references when applying conditional formatting.

Before you start

Before configuring conditional formatting, ensure that you have defined all necessary named ranges in your workbook and that the cells containing your dates are formatted as actual date values rather than text.

Solution 1Recommended

Verify Named Ranges and Fix #NAME? Errors

Resolve the #NAME? error by ensuring your formula references exist in the Name Manager before applying them to conditional formatting.

A #NAME? error in conditional formatting almost always means the formula is looking for a defined name (like a specific cell or range) that does not exist or is misspelled.

Open the Name Manager

Go to the Formulas tab on the Excel ribbon, then click on Name Manager.

Check missing references

Look for the named ranges used in your shift formula, such as 'Job1_Pattern' and 'Job1_StartDate'. If they are missing, click 'New' and define them by selecting the appropriate cells.

Validate your date cells

Verify that the date cell (for example, J5) contains a valid date and is not earlier than your defined 'Job1_StartDate'.

Test formula in a standard cell

Type your shift formula into a standard worksheet cell first. Once you confirm it calculates correctly without producing a #NAME? error, copy the formula into your conditional formatting rule.

Pro Tip: Testing complex formulas in a standard cell before using them in conditional formatting helps isolate and fix syntax errors much faster.

Solution 2

Set Up Conditional Formatting for Repeating Shifts

Create individual conditional formatting rules for each shift pattern to assign distinct colors dynamically.

Seamless Spreadsheet Management

Create Shift Calendars Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful Name Manager and Conditional Formatting tools, making it easy to build dynamic shift calendars without formula errors.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your shift calendar document.
2. Define your ranges: Go to the Formulas tab and select Name Manager to define 'Job1_Pattern' and 'Job1_StartDate'.
3. Access conditional formatting: Select your calendar grid, click the Home tab, and choose Conditional Formatting.
4. Apply color rules: Add new rules based on cell values or formulas to assign a unique color to each of the seven shifts.

Fully compatible with Microsoft Excel (.xlsx) formats and advanced conditional formatting rules.Intuitive Name Manager interface to easily track, edit, and modify defined ranges.Support for multiple overlapping conditional formatting rules to color-code your shifts effortlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula return a #NAME? error?

A #NAME? error typically occurs when a formula refers to a named range that hasn't been defined, or if there is a typo in a function name. Check your Name Manager to ensure all referenced names exist and are spelled correctly.

Can I apply more than three conditional formatting rules in a spreadsheet?

Yes, modern spreadsheet programs allow you to apply dozens of conditional formatting rules to the same cell, making it possible to color-code up to seven or more different shift patterns.

How do I make a cell blank if the calendar date is invalid?

You can wrap your shift calculation in an IF function to check if the date cell is less than your start date or invalid. If the condition is met, instruct the formula to return an empty string ("").

Excel & Data Problems