Data Validation – TIME

How to limit user to input TIME only?

Sometime, actually too many times, users may not input cell content in a way you want them to.  As a result, the formula you set for calculation may go wrong; and you may go crazy… ;p  The screenshot below shows an example of miscalculation due to incorrect TIME input.

Excel Tips - Data validation TIME

To minimize (*not to avoid) the chance of such human error, DATA VALIDATION is the tool for you.

1) Select the range of cells you want to limit user input, then go to Data Tab –> Validate –> Data Validation…

Excel Tips - Data Validation Time 0.1

On the Settings Tab,

  • Allow: Time
  • Data: between
  • Start time: 00:00
  • End time: 23:59

Excel Tips - Data Validation Time 1

2) Then go to the Error Alert tab where you can set the pop-up message when invalid data is input.

  • Ensure the “Show error alert…” is checked
  • Style: Stop (to enforce user to input only the validated data; if you select other options, you leave room for user to input any value)
  • Title: Your title
  • Message: Your message
  • Click OK

Excel Tips - Data Validation Time 2

3) Done.  Now if an invalid data, say 8.5, is input, Excel won’t take that number and show you the error message.

Excel Tips - Data Validation Time 3

Easy, right?

*Maybe you may ask why I said “to minimize (not to avoid) chance of such human error”.  It is because many users (hope you are not one of them) have tendency of breaking rules… they could override the data validation by “copy and paste” data to the cell.  Yes, Data validation works for manual input only.

A question for your thoughts:  Is it possible to limit user to input time of 15-mins interval, i.e. 00:15, 00:30, 00:45, 01:00 and so on…? 


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

1 Response to Data Validation – TIME

  1. Pingback: Allow time input at 15-min intervals only – Data Validation | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.