DATA Validation for Military Time help requested

[Endosmok]

So I have an excel sheet that people enter time in military format.
In the FORMAT CELLS, I have it set to CUSTOM -> h:mm
I also have some calculations for time which are working fine.

But I want to make sure that users are only entering numbers for time. So I went to data validation.

When I select the default TIME for data validation, I get an error on my ending time range.

Maybe the default one doesn't like Military Time?

I have BETWEEN Start 0000 and I try End 2359 (I also tried 235959) and excel says end time is not valid.

Any ideas on how to get data validation working with military time?

Thanks

 

[TonyUK72]

Personally I want total control over what my users put into my sheets. So I would......

Format ALL time cells as text and use the following formula in calculations to get the desired results......

Please remove speech marks at the beginning and end

"=LEFT(A1,2)&":"&RIGHT(A1,2)"