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

[Ark68]

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.

 

[jasonb75]

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.

 

[Ark68]

Thank you Jason. I will ensure users only enter time in either 24 hour format or AM/PM.