Validating data

13-Aug-2019 20:39

validating data-31

officedating com

Microsoft Excel has several built-in data validation rules for numbers, dates and text, but they cover only the most basic scenarios.If you want to validate cells with your own criteria, create a custom validation rule based on a formula.In cell C4, the employee will enter a starting date for the vacation.To ensure that a valid date is entered in cell C4, data validation is used, to set a start date and end date. Type start and end dates in the rules Enter start and end dates on a worksheet To ensure that they enter a date in the year 2017, you can apply data validation to that cell, and type a start and end date in the rules.

If you use a formula-based criteria in a built-in rule (like we did to validate times based on the current time), it can also return another numeric value.

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.

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.

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.