Others

How to Fix Microsoft Lists Date Validation for the Next Working Day

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to configure a date column to only accept future dates that fall on a weekday, but their current formula is resulting in a #NAME? error.

Product: Microsoft Lists
Device & OS: not provided
Scenario: Setting up column validation rules to restrict date entries to future working days while excluding weekends.
Observed behavior: The current validation formula displays a #NAME? error instead of correctly validating the input date.

Before you start

Ensure you have list owner or edit permissions in Microsoft Lists to modify column properties, and verify that the column you are validating is explicitly set as a Date and Time column type.

Solution 1Recommended

Apply the Correct Weekday Formula and Verify Internal Column Names

Fix the #NAME? error by ensuring the formula references the exact internal column name and uses the correct parameters to evaluate weekdays.

The #NAME? error typically occurs when a formula references a column name that does not precisely match the system's internal name (e.g., spaces or capitalization issues).

By utilizing the WEEKDAY function with a return type of 2, Monday is evaluated as 1 and Sunday as 7. Setting the condition to be less than 6 effectively ensures the chosen date is between Monday and Friday.

Access List Settings

Navigate to your Microsoft List, click the gear icon in the top right corner, and select List settings.

Edit the Date Column

Scroll down to the Columns section and click on your specific Date column (e.g., StartDate) to open its property settings.

Update the Validation Formula

Expand the Column Validation section at the bottom. Enter this formula exactly: =AND([StartDate]>TODAY(),WEEKDAY([StartDate],2)<6).

Set User Message and Save

Enter a clear error message in the User Message box, such as 'Please enter a future weekday date,' and click OK to save your changes.

Holiday Limitations: This formula successfully excludes Saturdays and Sundays but does not automatically account for public holidays or guarantee a full 24-hour working day has passed.

Free Microsoft Office alternative

Manage Data and Project Schedules Effectively with WPS Office

While Microsoft Lists handles web-based list tracking, WPS Spreadsheet provides a powerful, offline-capable alternative for data validation and schedule tracking. As a comprehensive and free Office suite, it enables you to apply complex validation rules seamlessly without encountering web-based formula errors.

1. Select Your Date Cells: Open WPS Spreadsheet and highlight the cells or column where users will enter dates.
2. Open Data Validation: Go to the Data tab on the top ribbon and click on the Validation button.
3. Apply Custom Formula: In the Allow drop-down menu, select Custom, and enter the formula =AND(A1>TODAY(), WEEKDAY(A1, 2)<6) (assuming A1 is your active cell).
4. Confirm Settings: Click OK to apply the validation rule and prevent weekend date entries in your spreadsheet.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) for advanced list management.Robust Data Validation features to easily restrict date inputs to future weekdays.Lightweight installation with a user-friendly, familiar spreadsheet interface.Built-in templates specifically designed for project tracking and schedule management.Completely free to download and use for daily office productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Microsoft Lists validation formula return a #NAME? error?

The #NAME? error usually occurs when the formula references a column name that doesn't exist or is formatted incorrectly. Ensure your formula uses the exact internal column name enclosed in brackets, such as [StartDate], taking into account any spaces or special characters.

Can I use the WEEKDAY function to exclude weekends in Microsoft Lists?

Yes, the WEEKDAY function is fully supported for column validation. By using WEEKDAY([ColumnName], 2), the system counts Monday as 1 through Sunday as 7. Validating the result as <6 ensures that only Monday through Friday are accepted.

How do I account for public holidays in my date validation formula?

Standard column validation formulas in Microsoft Lists cannot automatically check for localized public holidays. To strictly exclude holidays, you would typically need to build a Power Automate flow to validate the submitted date against a custom SharePoint holiday list.

Office Quick Solutions