Data Validation errors with leading zeros?

EdNerd

Active Member
Joined
May 19, 2011
Messages
405
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:
Excel 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:
Rich (BB code):
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,594
Office Version
  1. 365
Platform
  1. Windows
Hi, here's another custom data validation option that you could also try.

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

Forum statistics

Threads
1,147,450
Messages
5,741,189
Members
423,647
Latest member
lyanndominique

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
Top