Hi there, in this continued series of data validation exercises, I have a task in this video, the task is in the column B, all the data that is entered by the end user must be between first January of 2012. And before or on 31st, December 2012. That is the range. Now, she at this moment can give a date which you may be outside this range correct. I want to have an input validation, data validation, which quickly pops up with an error saying that this is not allowed. Now, this is very useful in case you're preparing some kind of a form, and you expect the user to be making sure the input is in within the desired range.
So now let's see how to populate that particular data validation. I choose the range of cells where the users are expected to feed in the date. I go to Data tab. Then I go to data values. And I click on Data Validation. Okay.
Amongst the option which says allow any value, I go ahead and choose date allow date that lies between. And you notice the option does say greater than or equal to or less than or equal to, but at this moment, what we are going to choose is between, say choose between and make sure that the date that you're giving is in the format which Excel understands. So if my current default settings of date is MD vi format, I will give the date in that particular format. So I give one slash one slash 2012. And the end date has to be twel indicating December slash 31, slash 2012. Now once I've done so, I press OK. And let me test this first April 2030.
Job. It gives me an L which says the value you entered is not valid. A user has restricted values that can be entered into the cell. So it asks you to redraw, in which case this time, let me give first April 2012. And if you press enter, there you go, it has accepted. And not only that, you can also populate a message box, which defines the exact nature of an error and exact action that needs to be taken.
So I choose the same thing again, I go to data, I go to data validation. I keep the settings same, but I go to error alert in the error alert. I have a message saying in valid date. And then in the error message, I say you must enter a date from the year 2012. Let me now press OK. And let me test this with a revised data which is falling outside the range. So first of June 2013, let's say Aha, this time, it's a Customize message that is given to me It says invalidate, you must enter a date from the year 2012.
So there you go. This is the basic form of data validation with respect to dates, although Be cautious that if somebody has written a date outside this range, and he copies that cell and paste as a special value is a special value, okay? It accepts that, given the fact that the data validation still exists. So if you're getting such values, and you're not sure whether anybody has used a special value, then you may choose the entire range, go to data, data validation and say circle invalid data. In case it is not as per the rules, it will circle that and once you've found that out, you can go ahead and again say clear Validation Service.