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

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

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

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

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

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

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

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

Niklas wrote:
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

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

Hi

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


--
(When sending e-mail, use address )
Arvi Laanemets


"Niklas" wrote in message
...
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

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

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





"Niklas" schrieb im Newsbeitrag
...
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

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

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





"Niklas" schrieb im Newsbeitra
..
The highest time Data Validation accepts is 23:59:59 or have I misse
something
Regard
/Nikla
----- arno wrote: ----
Hi Niklas
In a text cell I want to check if the user have entered a vali
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
regard
arn

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

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

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

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" 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

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

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


"onedaywhen" wrote in message
om...
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" 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