Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
I am trying to validate data using the Data Validation interface. I
want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
You can use a list but uncheck the "In Cell Dropdown" box.
Or you could use Custom instead of list and use this formula: =OR(A1="AM",A1="PM") hth, Doug "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
Can you restrict them to a 12 hour window so there is no ambiguity?
The cell doesn't store the AM/PM. It is stored a decimal value between 0 and 1. 0.25 is 6 AM, .5 is 12 Noon, .75 is 6 PM. So validation isn't going to be able to test on an AM or PM entry in the cell because there won't be one - unless you format the cell as Text, then it will be more difficult to work with as a date to do calculations. -- Regards, Tom Ogilvy "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
Just for clarification, Doug is talking about entering the Text AM or PM in
a separate cell. That may very well be what you were asking. I was answering in the context of entering a time in a cell and specifying AM or PM as part of the time value. -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... You can use a list but uncheck the "In Cell Dropdown" box. Or you could use Custom instead of list and use this formula: =OR(A1="AM",A1="PM") hth, Doug "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
Thanks, Tom. I have a user input sheet with 3 separate columns for
hour, min, and AM/PM. I then convert the entries on a separate sheet to the proper time using the timevalue function. We did several tests with the users to see what input format would be the fastest for them, as they must input data for hundreds of employees and only have a specified time frame to do so. What we found is that if the user must enter 08:30 AM, typing the semicolon slows them down significantly and produces many errors. The fastest way for them to enter the time is to enter the hour as a number, tab, then enter the min as a number, tab, then enter AM or PM (which is already defaulted) to AM so 9 times out of 10 they simply have to tab again and not enter anything. The 12 hour check is done separately in the program. Thanks again. Tom Ogilvy wrote: Can you restrict them to a 12 hour window so there is no ambiguity? The cell doesn't store the AM/PM. It is stored a decimal value between 0 and 1. 0.25 is 6 AM, .5 is 12 Noon, .75 is 6 PM. So validation isn't going to be able to test on an AM or PM entry in the cell because there won't be one - unless you format the cell as Text, then it will be more difficult to work with as a date to do calculations. -- Regards, Tom Ogilvy "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
Doug - Thanks profusely. This works beautifully, I figured it was
something relatively simple, but for the life of me I couldn't get it to work. Maybe I'm having a bad day. Thanks again. Doug Glancy wrote: You can use a list but uncheck the "In Cell Dropdown" box. Or you could use Custom instead of list and use this formula: =OR(A1="AM",A1="PM") hth, Doug "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
My hat is off to Doug then. That is the last interpretation I would make of
your question. Unfortunately Doug's response wasn't visible when I posted or I wouldn't have wasted the time. I suspose your employees must be using the keypad. -- Regards, Tom Ogilvy "Connie" wrote in message ups.com... Thanks, Tom. I have a user input sheet with 3 separate columns for hour, min, and AM/PM. I then convert the entries on a separate sheet to the proper time using the timevalue function. We did several tests with the users to see what input format would be the fastest for them, as they must input data for hundreds of employees and only have a specified time frame to do so. What we found is that if the user must enter 08:30 AM, typing the semicolon slows them down significantly and produces many errors. The fastest way for them to enter the time is to enter the hour as a number, tab, then enter the min as a number, tab, then enter AM or PM (which is already defaulted) to AM so 9 times out of 10 they simply have to tab again and not enter anything. The 12 hour check is done separately in the program. Thanks again. Tom Ogilvy wrote: Can you restrict them to a 12 hour window so there is no ambiguity? The cell doesn't store the AM/PM. It is stored a decimal value between 0 and 1. 0.25 is 6 AM, .5 is 12 Noon, .75 is 6 PM. So validation isn't going to be able to test on an AM or PM entry in the cell because there won't be one - unless you format the cell as Text, then it will be more difficult to work with as a date to do calculations. -- Regards, Tom Ogilvy "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
I think you understood the question (although I probably didn't state
it well), you were just thinking ahead. Boy you're smart! The employees are using the keypad. Tom Ogilvy wrote: My hat is off to Doug then. That is the last interpretation I would make of your question. Unfortunately Doug's response wasn't visible when I posted or I wouldn't have wasted the time. I suspose your employees must be using the keypad. -- Regards, Tom Ogilvy "Connie" wrote in message ups.com... Thanks, Tom. I have a user input sheet with 3 separate columns for hour, min, and AM/PM. I then convert the entries on a separate sheet to the proper time using the timevalue function. We did several tests with the users to see what input format would be the fastest for them, as they must input data for hundreds of employees and only have a specified time frame to do so. What we found is that if the user must enter 08:30 AM, typing the semicolon slows them down significantly and produces many errors. The fastest way for them to enter the time is to enter the hour as a number, tab, then enter the min as a number, tab, then enter AM or PM (which is already defaulted) to AM so 9 times out of 10 they simply have to tab again and not enter anything. The 12 hour check is done separately in the program. Thanks again. Tom Ogilvy wrote: Can you restrict them to a 12 hour window so there is no ambiguity? The cell doesn't store the AM/PM. It is stored a decimal value between 0 and 1. 0.25 is 6 AM, .5 is 12 Noon, .75 is 6 PM. So validation isn't going to be able to test on an AM or PM entry in the cell because there won't be one - unless you format the cell as Text, then it will be more difficult to work with as a date to do calculations. -- Regards, Tom Ogilvy "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation without using List feature
No, I understood the question to be entering a time value in a single cell.
Not what you asked at all. -- Regards, Tom Ogilvy "Connie" wrote in message ups.com... I think you understood the question (although I probably didn't state it well), you were just thinking ahead. Boy you're smart! The employees are using the keypad. Tom Ogilvy wrote: My hat is off to Doug then. That is the last interpretation I would make of your question. Unfortunately Doug's response wasn't visible when I posted or I wouldn't have wasted the time. I suspose your employees must be using the keypad. -- Regards, Tom Ogilvy "Connie" wrote in message ups.com... Thanks, Tom. I have a user input sheet with 3 separate columns for hour, min, and AM/PM. I then convert the entries on a separate sheet to the proper time using the timevalue function. We did several tests with the users to see what input format would be the fastest for them, as they must input data for hundreds of employees and only have a specified time frame to do so. What we found is that if the user must enter 08:30 AM, typing the semicolon slows them down significantly and produces many errors. The fastest way for them to enter the time is to enter the hour as a number, tab, then enter the min as a number, tab, then enter AM or PM (which is already defaulted) to AM so 9 times out of 10 they simply have to tab again and not enter anything. The 12 hour check is done separately in the program. Thanks again. Tom Ogilvy wrote: Can you restrict them to a 12 hour window so there is no ambiguity? The cell doesn't store the AM/PM. It is stored a decimal value between 0 and 1. 0.25 is 6 AM, .5 is 12 Noon, .75 is 6 PM. So validation isn't going to be able to test on an AM or PM entry in the cell because there won't be one - unless you format the cell as Text, then it will be more difficult to work with as a date to do calculations. -- Regards, Tom Ogilvy "Connie" wrote in message oups.com... I am trying to validate data using the Data Validation interface. I want to make sure the user enters either AM or PM, but I don't want to use a list. I want the user to type the data in. Is there a way to do this? Thanks. Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
How to feed dynamic information to a drop down List (Validation Feature)? | Excel Worksheet Functions |