top of page

You are learning Data Validation in MS Excel

How to use data validation to restrict data entry to a specific time range (e.g., business hours)?

Here's how to use data validation to restrict data entry to a specific time range (e.g., business hours) in Excel:

1. Select the Cells:
- Choose the cell(s) where you want to allow users to enter time data within a specific range.

2. Apply Data Validation:
- Go to the "Data" tab on the Excel ribbon.
- In the "Data Tools" group, click "Data Validation."

3. Set Allow to Time:
- In the "Settings" tab of the Data Validation window, under "Allow," choose "Time" from the dropdown menu.

4. Define the Time Range:
- You have two options to restrict the time entries:
* Between: This allows entries only within a specific time window.
- In the "Start time" box, enter the beginning of your business hours (e.g., "08:00").
- In the "End time" box, enter the end of your business hours (e.g., "17:00").
* Less than or Greater than:
- Choose "Less than" if you only want entries before a specific time (e.g., before work starts: "08:00").
- Choose "Greater than" if you only want entries after a specific time (e.g., after work ends: "17:00").

5. Additional Options (Optional):
- Input Message: (Optional) In the "Input Message" box, you can type a custom message that will appear when users select the cell. This can explain the allowed time range (e.g., "Business hours only: 8:00 AM to 5:00 PM").
- Error Message: (Optional) In the "Error Alert" dropdown, choose "Show error message" and type a message that will appear if users try to enter an invalid time (e.g., "Time outside business hours").

6. Click OK:
- Once you've set your desired options, click "OK" to close the Data Validation window.

Now, when users try to enter data in the selected cell(s), they will be restricted to the time range you defined. If they enter an invalid time, they will receive the error message (if you set one).

bottom of page