Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Limiting time entries with formulas

From: Excel 2010: Data Validation in Depth

Video: 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.

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.

Show transcript

This video is part of

Image for Excel 2010: Data Validation in Depth
Excel 2010: Data Validation in Depth

17 video lessons · 9122 viewers

Dennis Taylor
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Excel 2010: Data Validation in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.