It can be a challenge to make data entry consistent in concurrent use. A common example is entering a future date into a form. In this article, we are going to show you how to prevent future dates in Excel.
To limit the user interaction with the input cells, you can define validations. Data validation often used for creating drop down lists, but they can also be used for limiting user inputs in other input types as well. Let’s see how you can set a validation to prevent future dates in Excel.
- Select the cell(s) you want to make date inputs.
- Activate the Data tab in the ribbon
- Click the Data Validation
- In the Settings tab, set Allow as
- Data can be either less than or less than or equal to. This is essentially whether you want to include the present day in validation.
- End Date should =TODAY() function to retrieve the present date.
- (Optional) Activate the Error Alert tab and modify the error message.
- Click OK button to apply the validation.
Once validation is configured; entering any date after the present day will give an error, and prevent users from continuing with their selection.
Retry: Returns to the cell with invalid value.
Cancel: Removes the invalid value from the cell.
The formula for preventing future dates
Although you can enter any date into the End Date or any date related field in the Data Validation dialog, using the TODAY formula makes the validation dynamic. In this example, static date values are more suitable for setting a lower limit. For example, if you do not want users to enter a date before the year 2010, use 1/1/2020 as Start Date.
Error Alert
Changing the Error Style can help you further customize the warning. Any option other than Stop, however, gives user the ability continue with an invalid value entered. These options are good if you want to keep validation for information only. Otherwise, leave it at Stop to prevent invalid entries altogether.
