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.…
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
Share this video
Embed this video
Video: Time limitations with basic controls