A few years ago, I did a post on using Data Validation to produce drop-down lists. However, Data Validation allows for many other ways to control the data entered and I thought I would cover one of those here - how to control the entry of dates to within a certain range.
To open the Data Validation window, select the range that you want to apply the validation to and click Data Validation on the Data ribbon (select Data Validation from the resulting drop-down).
From the "Allow" drop-down at the top of the window, select Date, choose what the operator from the next drop-down (there a number of options here such as greater than, less than, etc. but in most cases we will choose "between").
We then enter the earliest and latest dates that we wish to allow. e.g.
It would be good practice to enter these as cell references and to enter the dates in the cells, so that the range is clearly visible and can be edited by a user.
This will present the user with an error message if they try to enter a date outside of this range (or if they try to enter anything that isn't a valid date at all), and will not allow them to leave the cell without entering a valid date within the range.
As with all Data Validation, you can use the other two tabs to enter an Input Message that will appear when you click in the cell and/or create a custom Error Alert for when the data entered is invalid.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
No comments:
Post a Comment