Data Validation errors with leading zeros?

[EdNerd]

We work on a 24-hour clock system. We input times as a four-digit number as hhmm (no colon). To help and remind users that this needed to be a complete four-digit time, I formatted the cell with Custom >> 0000, and used the following Data Validation:
Allow: Custom
Formula:

=AND(ISNUMBER(E31),LEN(E31)>=3,LEN(E31)<5)

This works okay for times after 0100. But with times from 0001 - 0059, I get my error message:

Time values must be four digits, including any leading zeros.

Apparently, the Validation is looking at the number without applying the leading zeros to the LEN. The Validation sees only the one or two digits following the leading zeros, says it's too short, and will not allow the input.

Any suggestions to correct this issue?
Ed

 

[RoryA]

Custom formatting does not alter the actual value of the cell. You could use the TEXT function in the DV formula instead of the value - ie use TEXT(E31,"0000") rather than just E31.

 

[EdNerd]

Never mind!! Apparently I asked the same question 6 years ago -- and just never implemented the answer!! ?
See here: https://www.mrexcel.com/board/threads/get-data-validation-to-recognize-0003-as-a-four-digit-number.839568/

 

[FormR]

Hi, here's another custom data validation option that you could also try.

=E31=(0+TEXT(TEXT(E31,"00\:00"),"hhmm"))