Data Validation – TIME - KING OF EXCEL

Tuesday, December 31, 2019

Data Validation – TIME

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…? 
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Popular Posts