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

[EdNerd]

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

 

[Scott Huish]

Try this:

=AND(ISNUMBER(E6+0),LEN(TEXT(E6,"0000"))=4)

 

[shg]

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).

 

[InaCell]

Hi EdNerd

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

InaCell

 

[EdNerd]

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

 

[InaCell]

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.