Data Validation - Numeric to include "zeros"

**pogieboy** · 08-27-2012, 09:31 AM

Below is the formula that I would like to use with only one major modification Where "zeros" should show too. Not on empty cells, only if it is input.
The input reprsents time in 24 hour format. Such as 1300, (hrs), 2300, 0600, 0000 (instead of 2400). When I input any number that is preceded by a "0", (I.e. 0700, 0230, 0000,), I receive an error message. all other numbers not preceded by "0", works fine.
Any help on modifiying the formula to allow the cell to accept numbers preceded by "0", is greatly appreciated.

Here's the formula:

=AND(ISNUMBER(A1),LEN(A1)=4)

Thank you.

**K m** · 08-27-2012, 09:42 AM

The time preceeded by zeros is interpreted as text.
VALUE will convert the text to numbers ie:
=value(A1)

Try this:
=AND(Value(A1), LEN(A1)=4)

**bongaan** · 08-27-2012, 09:43 AM

If it's preceded by a zero then it's not a number anymore (in the strict sense). You can change your data validation to Whole Number between 0 and 2359 and format your cells as Custom and enter 0000, this will automatically add a zero infront if not entered.