Limiting time entries with formulas
Video: Limiting time entries with formulasIn this worksheet, a validation rule has been set up in column B to restrict all the dates to the year 2011. In column C, there has been set up a time validation rule that makes sure that all of the times are from 6 a.m. until just short of 8 p.m.: before 8 p.m., 6 a.m., and thereafter. Someone has decided that it's going to be a little bit easier to read under tabulate information if the actual minutes that occur in these times are multiples of 15-- in other words, if these were 4:30, 4:45, 10:30, 10:45, 11 o'clock, 11:15.
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.
- Testing for whole numbers and decimals
- Using the input message box
- Sequencing and placing lists
- Creating multi-tiered lists
- Setting date and time limitations
- Limiting text length
- Locating data validation rules
Limiting time entries with formulas
In this worksheet, a validation rule has been set up in column B to restrict all the dates to the year 2011. In column C, there has been set up a time validation rule that makes sure that all of the times are from 6 a.m. until just short of 8 p.m.: before 8 p.m., 6 a.m., and thereafter. Someone has decided that it's going to be a little bit easier to read under tabulate information if the actual minutes that occur in these times are multiples of 15-- in other words, if these were 4:30, 4:45, 10:30, 10:45, 11 o'clock, 11:15.
We want all these entries to have the minutes to be either zero, 15, 30, or 45. Now you may or may not know that there is a way to calculate actual minutes here, and this particular function right here will simply show us the minutes of a particular data entry, 37, and we can see quickly here a 32 and 45. Now the original data validation rule here--click column C, Data Validation on the Data tab--restricts the entries.
You see the timeframe here. Under Validation criteria, we use Time. Now, if we also want to control the minute entry here, it's going to require a formula, and we'll have to use the Custom Validation criteria entry here instead. So we still need to incorporate this idea that we want to keep them within that timeframe. So I think it's best when we do this sort of thing to prepare the formula outside of the Data Validation dialog box, and then paste it in.
So perhaps starting right here--and I could drag this up even though C1 doesn't have a date, and we are going to be using this as one of our three criteria. We want to use the AND function to encompass all of these. So in English, we would like to say that the minute entry of all of the dates that we have here are going to be evenly divisible by 15, and you might or might not be familiar with a function called MOD. The MOD function allows us to calculate the remainder in a function.
So let's say that we will have a value here. It's going to be one through 60. The MOD function allows us to say, if we divide this value by 15, we're going to get a remainder. We want to make sure that this remainder is always zero. So if the number is a 15, we divide by 15. The remainder is 0. If it's a 30, we divide by 15. The remainder is 0. And so on. So that's quite a bit happening here all at once, but this is one of the three criteria we need. The other two relate to the time.
There is another function called Hour. We'll choose Hour. We want the hour of C1 to be greater than or equal to 6, meaning 6 a.m., comma and a similar construction, so I'll just copy this and change it slightly. Ctrl+C here. Click here, Ctrl+V. We also want to make sure that the hour is less than 20, and then put in the less than symbol here. So, these three criteria. Now, the formula we're going to paste in to the data validation rule, but just to check it out here again, the three things we're trying to do is to make sure that the minute entry is evenly divisible by 15, that the hourly entry is greater than or equal to 6--meaning 6 a.m.--and the hourly entry is before 8 p.m. is less than or equal to 20.
That's the hour of the day. Now we wouldn't really leave the formula here for now. I am going to put a space in front of it, so we can--at least for a while--highlight all of this to copy it, Ctrl+C, and simply press Enter to put it here for the moment. Let's come back to column C, Data Validation. We're not going to be using the existing criteria the way it's structured here. We want to use Custom. And instead of this entry here, I am pressing Ctrl+V to paste in the long formula that you just saw.
So there is our new formula. We'll click OK. And this currently means that simply cell C1 doesn't work. And we don't care about that. We do want to continue. Now, data validation does not do anything with existing data, so we'll have to change these manually. Let's make a new entry here. Here is another entry and it's 1:45 p.m. How about 1:47 p.m.? In other words, we'll violate the rule. You can type it this way. That's a valid entry under normal circumstances, but not here. It's not valid.
Let's do a retry. Change that to 5, press Enter, and it does work, and we can try a few more just to test it out. It's also a valid way to type an entry if you simply type in, if it's only hour, for example--- What if it's 2 p.m.? 2 space p is a valid way to type these. There we go. Now also to accompany this, you should use formatting in column C that standardizes your entries. In the example here, I previously had it set up, but you could easily change it if you want to.
I'll right-click column C--one of many ways to get to Format Cells--and consider either this time format that uses the a.m. p.m., or the previous one that uses the 24 hour style. Whatever fits your needs best here, that should be done also, either before or after you apply the data validation. But in column C, we've got a pretty substantially long formula. At first, it looks a little complicated, but ultimately, it's doing three things. It's making sure that any minute entry that we put in here, once we've set up the rule, is going to be an even multiple of 15, and the hours are going to be within 6 the 6 a.m. to 8 p.m. timeframe.
So in the examples here, we can jump back in and change them. And, by the way, we can't change them to a 6, or a 3 or something like that for the minute. This will not work. But we certainty could change it to a zero or make it be 45. If it's as long as a multiple of 15 and to be consistent, we would do this one as well, too, probably change it to that. So quite a few elements of creativity you can use when setting up time formulas by way of data validation.
There are currently no FAQs about Excel 2010: Data Validation in Depth.