Formating h:mm AM/PM

I have a time sheet that requires a space be enter between the time and AM or
PM, 1:00 PM instead of 1:00PM. Entery without the space gives "#Value!" in
the destination cell. Is there a way of formating this so as the person doing
data entery can omit the space and yet the space shows after they hit enter,
or not there after they hit enter? Does it matter? I have tried formatting
without the space through custom formatting and that does not seem to change
anything. (xl2000 on xp)

On Thu, 6 Jan 2005 09:17:08 -0800, Mike R <[email protected]>
wrote:

>I have a time sheet that requires a space be enter between the time and AM or
>PM, 1:00 PM instead of 1:00PM. Entery without the space gives "#Value!" in
>the destination cell. Is there a way of formating this so as the person doing
>data entery can omit the space and yet the space shows after they hit enter,
>or not there after they hit enter? Does it matter? I have tried formatting
>without the space through custom formatting and that does not seem to change
>anything. (xl2000 on xp)
>

Excel date and time formats only alter how an entry is *displayed*. They have
no effect on how an entry is *entered*.

You will need a VB solution to do what you want; or you could use data
validation to ensure proper entries.


--ron

Thanks for your reply Ron I appreciate it. At least now I have the direction
that I need. Thanks again!!

"Ron Rosenfeld" wrote:

> On Thu, 6 Jan 2005 09:17:08 -0800, Mike R <[email protected]>
> wrote:
>
> >I have a time sheet that requires a space be enter between the time and AM or
> >PM, 1:00 PM instead of 1:00PM. Entery without the space gives "#Value!" in
> >the destination cell. Is there a way of formating this so as the person doing
> >data entery can omit the space and yet the space shows after they hit enter,
> >or not there after they hit enter? Does it matter? I have tried formatting
> >without the space through custom formatting and that does not seem to change
> >anything. (xl2000 on xp)
> >
>
> Excel date and time formats only alter how an entry is *displayed*. They have
> no effect on how an entry is *entered*.
>
> You will need a VB solution to do what you want; or you could use data
> validation to ensure proper entries.
>
>
> --ron
>

On Thu, 6 Jan 2005 16:05:01 -0800, Mike R <[email protected]>
wrote:

>Thanks for your reply Ron I appreciate it. At least now I have the direction
>that I need. Thanks again!!

Glad to help. Post back if you require anything more.


--ron