A better way to create this function. To long for Data Validation.

[Niklas]

Hi
In a text cell I want to check if the user have entered a valid interval. A valid intervall looks like this 33:01:00. The checks I want to do is
1) check if the length is 8
2) check if their are only numbers in the positions 1,2,4,5,7 and 8
3) check if position 1 and 2 is between 0 and 99
4) check if position 4 and 5 is between 0 and 59
5) check if position 7 and 8 is between 0 and 59

I have created a function that does this but it is to long to be used by the Data Validation control and I think/hope their is a better way to create the function. Any suggestions would be appreciated?

=IF( AND(LEN(B5)=8, NOT(ISERROR(VALUE(MID(B5,1,2)))), MID(B5,3,1)=":", NOT(ISERROR(VALUE(MID(B5,4,2)))), MID(B5,6,1)=":", NOT(ISERROR(VALUE(MID(B5,7,2))))), AND(VALUE(MID(B5,1,2))>=0,VALUE(MID(B5,1,2))<100, VALUE(MID(B5,4,2))>=0,VALUE(MID(B5,4,2))<60, VALUE(MID(B5,7,2))>=0,VALUE(MID(B5,7,2))<60), FALSE)

Regards
/Niklas

 

[arno]

Hi Niklas,

In a text cell I want to check if the user have entered a valid interval.

A valid intervall looks like this 33:01:00.

why don't you use a cell that is formatted as time, [hh]:mm:ss? then you
only have to validate data (menu Data) to be less than the "time" 100:00:00.

regards

arno

 

[Niklas]

The highest time Data Validation accepts is 23:59:59 or have I missed something
Regard
/Nikla

----- arno wrote: ----

Hi Niklas

In a text cell I want to check if the user have entered a valid interval

A valid intervall looks like this 33:01:00

why don't you use a cell that is formatted as time, [hh]:mm:ss? then yo
only have to validate data (menu Data) to be less than the "time" 100:00:00

regard

arn

 

[Frank Kabel]

Hi
try using the custome category in the data validation dialog and enter
the formula:
=A1<(100/24)
replace 100 with your maximum time

--
Regards
Frank Kabel
Frankfurt, Germany

The highest time Data Validation accepts is 23:59:59 or have I missed
something? Regards
/Niklas

----- arno wrote: -----

Hi Niklas,

In a text cell I want to check if the user have entered a

valid interval. A valid intervall looks like this 33:01:00.

why don't you use a cell that is formatted as time, [hh]:mm:ss?
then you only have to validate data (menu Data) to be less than
the "time" 100:00:00.

regards

arno

 

[Arvi Laanemets]

Hi

Format as Custom "[hh]:mm:ss" (or "[h]:mm:ss"), you were said! It makes a
difference


--
(When sending e-mail, use address [email protected])
Arvi Laanemets

The highest time Data Validation accepts is 23:59:59 or have I missed something?
Regards
/Niklas

----- arno wrote: -----

Hi Niklas,

In a text cell I want to check if the user have entered a valid

interval.
A valid intervall looks like this 33:01:00.

why don't you use a cell that is formatted as time, [hh]:mm:ss? then you
only have to validate data (menu Data) to be less than the "time" 100:00:00.

regards

arno

 

[arno]

the only trouble i see is that the entered time is lost and replaced by a
"date". instead, you could have 3 input cells (hr, min, sec), each with data
validation set to <60 or <100. this also denies entereing characters.

then you can calculate the time with a formula without annoying the users
that input the data. make sure you set the format to [hh] and rememder that
times are a fraction of a day, like 1,5 equals 36:00:00.

arno

The highest time Data Validation accepts is 23:59:59 or have I missed something?
Regards
/Niklas

----- arno wrote: -----

Hi Niklas,

In a text cell I want to check if the user have entered a valid

interval.
A valid intervall looks like this 33:01:00.

why don't you use a cell that is formatted as time, [hh]:mm:ss? then you
only have to validate data (menu Data) to be less than the "time" 100:00:00.

regards

arno

 

[Niklas]

I have now made my homework regarding Format codes and now all your advices works for me. BUT as you said, my users did not like that their data were changed and they got confused when they wrote one thing and something else were displayed. That was especially true when I used the format [hh]:mm and their entered seconds disappered. I think I have to try something..
The 3 input cells sounds OK if that was used from the begining. They have used the format hh:mm:ss for so long that they do not want to change to another method and they want Excel to display what they enter and nothing else. I found out that "Data validation" treated #Value! as False, so I could skip all IsNumeric tests. My function got smaller

=AND(LEN(B14)=8, MID(B14,3,1)=":", MID(B14,6,1)=":", AND(VALUE(MID(B14,1,2))>=0,VALUE(MID(B14,1,2))<100, VALUE(MID(B14,4,2))>=0,VALUE(MID(B14,4,2))<60, VALUE(MID(B14,7,2))>=0,VALUE(MID(B14,7,2))<60)

Is their a better way to check if a value is between 2 values than AND(VALUE(MID(B14,7,2))>=0,VALUE(MID(B14,7,2))<60))

ISERROR(VALUE(MID(B5,1,2))) seems to be a lot of code to check if it is a number. Is their a better way to do it

Regard
/Nikla

----- arno wrote: ----

the only trouble i see is that the entered time is lost and replaced by
"date". instead, you could have 3 input cells (hr, min, sec), each with dat
validation set to <60 or <100. this also denies entereing characters

then you can calculate the time with a formula without annoying the user
that input the data. make sure you set the format to [hh] and rememder tha
times are a fraction of a day, like 1,5 equals 36:00:00

arn

The highest time Data Validation accepts is 23:59:59 or have I misse something

Regard
/Nikla
----- arno wrote: ----
Hi Niklas

interval
A valid intervall looks like this 33:01:00

why don't you use a cell that is formatted as time, [hh]:mm:ss? the

yo
only have to validate data (menu Data) to be less than the "time 100:00:00

 

[onedaywhen]

Because you cross-posted to microsoft.public.excel.programming, here's
an approach that uses regular expressions:

Private Sub Test()
Dim m_oVbs As Object
Set m_oVbs = CreateObject("VBScript.RegExp")
With m_oVbs
.Pattern = "[0-9][0-9]:[0-5][0-9]:[0-5][0-9]"
MsgBox .Test("30:01:00")
End With
End Sub

--

Hi Niklas,

In a text cell I want to check if the user have entered a valid interval.

A valid intervall looks like this 33:01:00.

why don't you use a cell that is formatted as time, [hh]:mm:ss? then you
only have to validate data (menu Data) to be less than the "time" 100:00:00.

regards

arno

 

[Dana DeLouis]

Nice! Another similar way might be with "Like."

MsgBox "30:01:00" Like "[0-9][0-9]:[0-5][0-9]:[0-5][0-9]"
MsgBox "30:61:00" Like "[0-9][0-9]:[0-5][0-9]:[0-5][0-9]"

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Because you cross-posted to microsoft.public.excel.programming, here's
an approach that uses regular expressions:

Private Sub Test()
Dim m_oVbs As Object
Set m_oVbs = CreateObject("VBScript.RegExp")
With m_oVbs
.Pattern = "[0-9][0-9]:[0-5][0-9]:[0-5][0-9]"
MsgBox .Test("30:01:00")
End With
End Sub

--

"arno" <[email protected]> wrote in message

Hi Niklas,

In a text cell I want to check if the user have entered a valid

interval.
A valid intervall looks like this 33:01:00.

why don't you use a cell that is formatted as time, [hh]:mm:ss? then you
only have to validate data (menu Data) to be less than the "time" 100:00:00.

regards

arno