Viewers: in countries Watching now:
In Excel 2010: Data Validation in Depth, author Dennis Taylor shows how to use the data validation tools in Excel to control how users can input data into workbooks and ensure data is entered consistently and accurately. The course covers creating dropdown lists, preventing duplicate entries, and controlling the format of numeric data, dates and times, and text entered into worksheets. Exercise files are included with the course.
In this worksheet, there is a data validation rule for column B that restricts all of the dates to the year 2011. If this company only takes orders from 6 a.m. until 8 p.m., similarly, we'd like a rule in column C related to times that makes sure that all the time entries fall within that range. Click column C, on the Data tab, Data Validation. And under Validation criteria, Allow > Time, and similar to Values and Decimals and Data entries, we start off by at least exploring some of the choices here between a certain timeframe, equal to, greater than.
Certainly, equal to wouldn't make any sense here, but greater than, less than might apply sometimes between. So we're going to put in the Starting time of 6 a.m. For example, 6:00 would be the way to enter it. We could also put in 6 a.m. if we wished. Ending time, 8 p.m. We might type it this way. It should work too. Click OK. So put an entry in here, 6:30. We could type the entry that way. That's just fine. How about 6:30 p.m.? You could type it this way.
I would caution you though: probably you want to do some formatting here. You could type it this way if you wish, by the way. That's fine, too. But let's put in a time that's outside the frame. 4:30 is automatically interpreted as 4:30 a.m. That's outside the frame of the data validation criteria that we set up, so obviously that's not acceptable. How about 8:30 p.m.? Whether you type it 8:30 p, that is a valid entry if we didn't have data validation rules, but it's not acceptable here. If we type in 24-hour style--17:00, for example, that's 5 o'clock. Do it that way.
That's just fine. So a variety of techniques here for entering the data, but data validation is focused only on the values that you put in, not the display. So ideally, on the timeframes here, what you can do, either before or after setting up the data validation, simply right-click column C--one of many ways to get to a Format Cells--and put in a standard timeframe that fits your particular needs. The two most common ones are the second one, the so-called 24-hour style, or the third one that shows a.m. p.m.
Now, no matter how you type this, if the time is within the valid range, it's going to appear in this style. So a sale at 4:30 p.m. you could type it as 16:30. It will display this way. It's well within the timeframe. If you try and put in 8:30 p.m., type it this way. It's not going to work, as you would expect, and so on. So, easy to set up, and most of the time I think when you're using this particular data validation criteria set for Time, it's likely to be within a certain timeframe: beginning and ending time.
There are currently no FAQs about Excel 2010: Data Validation in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.