Limiting cells to be filled out by specific characters

[masud8956]

Hi all!

I have a range of cells, say A1:F30 where I want the users to be able to enter the characters 0 to 9 a colon [:] only. The cells are formatted as [h]:mm and the colon will always appear in between the numeric values e.g. 1:15, 2:45, 10:05 etc

I tried Data Validation with the following formula:

=IF(A1="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789:"))),FALSE,TRUE))

Problem is, the cells now accept nothing but numeric value but do not accept the colon while I am trying to insert some durations at those cells.

What modification do I need to make?

Thanks.

 

[DanteAmor]

You could validate a range of numbers.
Allow: Decimal. Betwen, minimum: 0.000694444444444444, maximum: 0.999305555555555.
This will only allow you 00:01 until 23:59.
Test it

https://www.dropbox.com/s/4d87397no5fabfb/valid time.jpg?dl=0

 

[masud8956]

Thanks for the nice solution. It will do the job for me.

Surprisingly, Data Validation with the formula

=ISNUMBER(A1)

also worked nicely.

 

[DanteAmor]

Thanks for the nice solution. It will do the job for me.

Surprisingly, Data Validation with the formula

=ISNUMBER(A1)

also worked nicely.

IsNumber will allow you any number, decimals, integers and more than 24 hours