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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
>>
>
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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
>> >
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >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
> >> >
> >

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >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
>> >> >
>> >
>