Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64828 Viewers
80 Video lessons · 124385 Viewers
52 Video lessons · 60313 Viewers
59 Video lessons · 46137 Viewers