How to Fix SharePoint Date Validation Formula Allowing Invalid Dates
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.

- 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.
Ensure you have the necessary List Owner or Site Owner permissions to edit list settings and modify column validation formulas in your SharePoint environment.
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.
Navigate to your SharePoint list, click on the gear icon (Settings) in the upper right corner, and select 'List settings'.
Under the 'General Settings' section, click on 'Validation settings' to apply rules across multiple columns.
In the Formula box, enter the following logic: =OR([Date Resolved]>=INT(Created),[Date Resolved]="").
Add a user message in the 'User Message' box explaining the rule, then click 'Save' to apply the new validation.

Enable 'Include Time' for the Date Column
If tracking the exact time is acceptable, enabling time on both columns ensures an apples-to-apples comparison without needing advanced formulas.
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. Download the Installer: Visit the official WPS website and download the free WPS Office installer.
- 2. Install WPS Office: Run the setup file and follow the on-screen instructions to install the lightweight suite.
- 3. Open Your Files: Launch WPS Office and instantly open your existing Word, Excel, or PowerPoint documents with perfect formatting.

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.




