Using the Data Validation option Time (under Allow on the Settings tab) you can restrict time entries to fall within a certain time frame or to be after or before specific times of the day. In addition, this feature automatically prevents impossible entries like 27:32 and 10:75 - times that don't exist.
- In the chapter four file,…we're looking at a worksheet called time.…The order dates are not there yet.…They'll come in later.…We're gonna restrict the entries though in column C.…We only make sales…between 8am and 6pm.…So let's put a restriction in column C…so that all the entries here, fall within that time frame.…Data validation on the data tab,…allow time,…between is already prompted,…in other cases of course,…might be greater than or less than.…
This case, we're looking at both kinds of entries.…The starting time, one way to type this…can be eight colon zero zero, that's one way.…End time, we could type 18 colon zero zero.…We could type six colon zero zero pm.…I think it's more consistent here…to just use the 24 hour style…so I'll put in 18 colon zero zero…and click OK.…Now, I'll type an entry here, maybe it's 8:45.…Totally acceptable.…It's 3:30 in the afternoon for the next one.…
Now if I type 3 colon thirty,…I might be thinking 3:30 pm,…but excel reads this as 3:30 am.…And so as I press enter here, that's not acceptable, retry.…
Author
Dennis Taylor
Released
9/19/2016- Controlling numeric input
- Using the input message box
- Setting up pick lists for quick, error-free input
- Creating multitiered pick lists
- Setting date and time limitations
- Limiting text length
- Requiring entries to be unique
- Locating cells with data validation rules
Skill Level Advanced
Duration
Views
-
Introduction
-
Welcome37s
-
-
1. Controlling the Limits of Numeric Data
-
Using the Error Alert tab2m 40s
-
2. Setting Up Drop-Down Lists
-
Creating drop-down lists5m 15s
-
List location and order9m 30s
-
Multitiered lists8m 11s
-
-
3. Date Controls
-
4. Time Controls
-
5. Text Length Controls
-
6. Specialized Custom Formula Controls
-
Data validation shortcomings5m 20s
-
Conclusion
-
Goodbye27s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Time limitations with basic controls