Data Validation errors with leading zeros?

EdNerd

Active Member
Joined
May 19, 2011
Messages
456
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
Hi, here's another custom data validation option that you could also try.

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

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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