Excel date validation provides quite a lot of predefined criteria to restrict users to entering only dates between the two dates you specify, greater than, less than, or equal to a given date.If you want more control over data validation in your worksheets, you can replicate the inbuilt functionality with a custom rule or write your own formula that goes beyond the built-in capabilities of Excel data validation.To limit the entry to a date within a specified range, you can use either the predefined Date rule with the "between" criteria or make a custom validation rule with this generic formula: Please notice that the boundary dates are locked with absolute cell references.To restrict a user to entering only weekdays or weekends, configure a custom validation rule based on the WEEKDAY function.In yesterday's tutorial we started to look at Excel Data Validation - what its purpose is, how it works, and how to use built-in rules to validate data in your worksheets.Today, we are going to take a step further and talk about the nitty-gritty aspects of custom data validation in Excel as well as experiment with a handful of different validation formulas.
To pin down existing entries that do not meet your data validation criteria, use the Circle Invalid Data feature as shown in How to find invalid data in Excel.
In many situations, if you select the Ignore blank box when defining the rule (usually selected by default) and one or more cells referenced in your formula is blank, any value will be allowed in the validated cell.
Here is an example in the simplest form: When setting up a formula-based Excel validation rule, please keep in mind that all cell references in your formula are relative to the upper left cell in the selected range.
Here's how: Optionally, you can add a custom input message and Error alert that will show up when the user selects the validated cell or enters invalid data, respectively.
Below you will find a few examples of custom validation rules for different data types. All Excel data validation rules, built-in and custom, verify only new data that is typed in a cell after creating the rule.