Time ENtry Options With Data Validation - Restricted to entry in 24H Format Only?

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I wish to use the time option in data validation to ensure the user enters a valid time value in the cell. If I enter a min value of 00:00:00 (midnight) and a max value of 23:59:59 (the end of the day), the validation works if the user enters the time in 24H format. However, if an unfamiliar user enters a value like 2:00 PM, or 2:00P the data validation flags an error. Is there a means in which the data validation is able to accept all three possibilities as acceptable? I know the obvious is to instruct the user to enter in a certain format, but I'm trying to avoid clutter with extraneous text and pop up input messages if possible.

Certainly VBA may be an option, but it seems like the wheel being reinvented and I suspect it would take considerable effort to trap all the potential errors. (non numerical entries, incorrectly formatted entries, invalid numbers for example). The data validation does that all, it just lacks in the flexibility how the time is entered it seems.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
However, if an unfamiliar user enters a value like 2:00 PM, or 2:00P the data validation flags an error.
The first one does work,as will any valid time when entered. The second example provided is not a valid time format so will be trapped as an invalid entry (non numeric) however you try to validate it.
 
Upvote 0
Solution
Thank you Jason. I will ensure users only enter time in either 24 hour format or AM/PM.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top