Get Data Validation to recognize 0003 as a four-digit number??

EdNerd

Active Member
Joined
May 19, 2011
Messages
456
I have a column for entering military-style times (based on the 24-hour clock) without the colon (or any other separator). And I have Data Validation to ensure my users enter a four-digit number for the time. The cell is formatted Special-"0000". The validation formula is:
=AND(ISNUMBER(E6),LEN(E6)>=3,LEN(E6)<5)

The problem is entering times like 0039 or 0006. Excel automatically ignores the leading zeros in counting digits, and only sees one or two digits, making this an invalid number.

Is there any way to correct the validation formula to account for leading zeros that were actually entered?

Ed
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Unless the cell is formatted as Text, I don't think there is any way to distinguish whether a user entered 6, 06, or 0006.

You could use data validation to force entry of a whole number between 0 and 2359. Not all of those would be valid times (e.g., 1492, when Columbus sailed the ocean blue).
 
Last edited:
Upvote 0
Hi EdNerd

Would it be ok to have ' at the start of the number? ie '0001

InaCell
 
Upvote 0
Thank you all for chiming in and helping me find a solution.
Starting with Scott's formula, and adding in more to account for shg's "1492", I came up with this:
=AND(ISNUMBER(E6+0),LEN(TEXT(E6,"0000"))=4,E6>-1,E6<2400,RIGHT(E6,2)*1<60)
--iIt's a number
-- it's four digits
-- it's greater than 0
-- it's less than 2400
-- the last two digits are less than 60

*Whew!!*
But it seems to work.

Thanks again!!
Ed
 
Upvote 0
Solution
G'day Ed

Works for me.

So did this, formatting the cell as you did, fill a col down from 1 to 59, 100 to 159, 200 to 259, 300 to 359, 400 to 459, ...2300 to 2359.

Validate as a list. ie if in Col A, =$A$1:$A$1439

Also works and always will.

Your formula is much easier to input though. Well done.

Cheers, InaCell.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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