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.

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…

On the Settings Tab,

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

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

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

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…?