Others

How to Fix SharePoint Date Validation Formula Allowing Invalid Dates

Adam DavisAdam Davis Nov 2, 2026 868 views

Question details

The user needs to configure a SharePoint list validation formula to prevent a target date column from being earlier than the creation date, while ignoring hidden time values and allowing blank entries.

How to Fix SharePoint Date Validation Formula Allowing Invalid Dates
Product
SharePoint
Device & OS
not provided
Scenario
Setting up list validation rules in SharePoint to ensure the logical chronological order of recorded dates.
Observed behavior
Dates that appear logically valid or equal on the same day compare incorrectly due to hidden time values, causing the validation to fail or allow invalid dates.
Before you start

Ensure you have the necessary List Owner or Site Owner permissions to edit list settings and modify column validation formulas in your SharePoint environment.

Solution 1Recommended

Use the INT Function to Ignore Time Values in Validation

Applying the INT function removes the hidden time portion from the 'Created' field, ensuring a clean date-only comparison while allowing the field to remain blank.

SharePoint system fields like 'Created' inherently store both date and time data. When you compare it to a 'Date Only' column, the exact time can cause an apparent match to fail.

By utilizing the INT function, you strip the time data, enabling an accurate date-level comparison. The OR function is used to ensure the system accepts empty values.

1
Access List Settings

Navigate to your SharePoint list, click on the gear icon (Settings) in the upper right corner, and select 'List settings'.

2
Navigate to Validation Settings

Under the 'General Settings' section, click on 'Validation settings' to apply rules across multiple columns.

3
Apply the Formula

In the Formula box, enter the following logic: =OR([Date Resolved]>=INT(Created),[Date Resolved]="").

4
Save the Changes

Add a user message in the 'User Message' box explaining the rule, then click 'Save' to apply the new validation.

Use the INT Function to Ignore Time Values in Validation
Formula Explanation: The OR statement ensures that if the field is left blank (""), no error is triggered. The INT(Created) function evaluates the creation timestamp as a whole number, effectively dropping the time fractions.
Free Microsoft Office alternative

Manage Your Office Documents Seamlessly with WPS Office

While SharePoint handles list validation and intranet needs, for daily document creation and spreadsheet management, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office. Enjoy a familiar interface and robust tools for Word, Excel, and PowerPoint files without the heavy subscription costs.

  1. 1. Download the Installer: Visit the official WPS website and download the free WPS Office installer.
  2. 2. Install WPS Office: Run the setup file and follow the on-screen instructions to install the lightweight suite.
  3. 3. Open Your Files: Launch WPS Office and instantly open your existing Word, Excel, or PowerPoint documents with perfect formatting.
Highly compatible with Microsoft Office formats (.docx, .xlsx, .pptx)Free and lightweight suite with a familiar user interfaceIncludes advanced spreadsheet formulas for robust data validation offlineSeamless migration with easy-to-use cloud synchronization features
microsoft office alternative - wps office

Frequently Asked Questions

Why does SharePoint include time in date-only columns?

System fields like 'Created' and 'Modified' always capture the exact date and time a record is generated, even if you do not display it. When a formula compares a 'Date Only' column with a system field, the hidden time fractions cause the values to differ.

How do I allow a blank date field in SharePoint validation?

You can wrap your validation logic in an OR function, such as =OR([ColumnName]="", [Your Logic]). This tells SharePoint to pass the validation check if the field is empty, or enforce the logic if a date is entered.

Can I use the TODAY function in SharePoint list column validation?

Yes, you can use the TODAY function in column validation (e.g., =[Date]>=TODAY()) to restrict entries to future dates. However, the TODAY function evaluates based on the server's current date and cannot be used inside standard calculated columns.