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

[Guest]

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

 

[Guest]

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 (e-mail address removed))
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

 

[Guest]

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

 

[arno]

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.

change to 3 cells plus data validation, this will do the job for everybody.
tell'em!

your format should be [hh]:mm:ss, you forgot the seconds. you can make the
displayed data disappear by protecting the sheet and hiding what's in the
cell (see format/cells/protection).

=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))?

what are you checking here? it's impossible to "enter" eg. 77 minutes, have
a try with 5:77:22 and look what happens!

hmm... one error anyway: a 2 digit number cannot be >=100.

by the way, are your users _typing_ in the data, or do they copy/paste?

regards

arno

 

[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

 

[Guest]

The users got confused when the formula field showed one thing and the cell another. Hiding the formula field was a nice move.

|what are you checking here? it's impossible to "enter" eg. 77 minutes, have
|a try with 5:77:22 and look what happens!
|
|hmm... one error anyway: a 2 digit number cannot be >=100.

You should not be able to enter 77 minutes. 77 minutes are 1 hour and 17 minutes and should be enterd as 01:17. The check was >= 0.

Regards
/Niklas

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

AND(VALUE(MID(B14,7,2))>=0,VALUE(MID(B14,7,2))<60))?



by the way, are your users _typing_ in the data, or do they copy/paste?

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