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

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

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

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

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

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

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

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

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