Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
Data Validation and a Long List. An Easier Way? | Excel Discussion (Misc queries) | |||
Search for a specific entry in a long data validation list | Excel Discussion (Misc queries) | |||
How do you create a list & data validation in same cell? | Excel Worksheet Functions | |||
Data Validation - Create dependent lists | Excel Discussion (Misc queries) |