Login
Password

Forgot your password?

Validate Time Entries in Excel

By Edited Aug 5, 2016 0 0

If you are using time values in an Excel spreadsheet, you may want to apply data validation to the cells to restrict the time values that can be entered. Validating entries helps to ensure that data is accurate in the spreadsheet.

This tutorial will look at setting up some basic time data validation and then creating custom criteria using formulas.

Basic Time Data Validation

As an example of validating time entries in Excel we will look at limiting the time entered to between the hours of 8 am and 5 pm.

1.  Select the cells you want to validate

2.  Click the Data tab on the Ribbon

3.  Click the Data Validation button in the Data Tools group

4.  Click on the Allow drop list arrow and select Time

5.  Ensure the Data field is set to Between

6.  Enter 8:00 in the Start time field and 17:00 in the End time field. The time can be entered in any time format accepted by Excel such as 8 am. You are not limited to the 24 hour clock

Time data validation in Excel

7.  Click Ok

Times entered in the range of cells will now be limited to between 8 am and 5 pm.

Use Formulas with Time Data Validation

Use formulas with data validation to create more complex criteria. For example, we may need to validate each time entry to ensure it is a multiple of 30 minutes e.g. 9:30, 10:00, 10:30. Times that are not a multiple of 30 minutes are not allowed.

1.  Select the cells you want to validate

2.  Click the Data tab on the Ribbon

3.  Click the Data Validation button in the Data Tools group

4.  Select Custom from the Allow drop list menu

5.   Enter =MOD(MINUTE(A1),30)=0 in the Formula field and click Ok

Use formulas in your Excel data validation

The MINUTE function is used to extract the minute from cell A1. The MOD function is then used to return the remainder from the minute extracted divided by 30 minutes. If the result is 0 we know the time is a multiple of 30 minutes.

If you are comfortable writing formulas then it adds another dimension to your data validation conditions. By using the different time functions available you can be sure to create the criteria you are looking for.

Advertisement

Comments

Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology