format time problem
 

Hi,

I have a column for enginners to enter the time an appointment starts. I
want this column to look like 10:00, 12:00, 14:00 and so on. Not those exact
numbers, just that format. I went to format cells and all the time formats
include seconds or dates so I went to custom formats. On here I found hh:mm
which is exactly what I need. The only problem is that if I simply enter 10
for instance meaning 10am (as many engineers will) for some reason the time
remains at 00:00 and a date is added before it, usually a date in Jan 1900.
Is there anyway of forcing it to only format to hh:mm?

format time problem
 

See http://www.cpearson.com/excel/DateTimeEntry.htm

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Hi,

I have a column for enginners to enter the time an appointment starts. I
want this column to look like 10:00, 12:00, 14:00 and so on. Not those
exact
numbers, just that format. I went to format cells and all the time formats
include seconds or dates so I went to custom formats. On here I found
hh:mm
which is exactly what I need. The only problem is that if I simply enter
10
for instance meaning 10am (as many engineers will) for some reason the
time
remains at 00:00 and a date is added before it, usually a date in Jan
1900.
Is there anyway of forcing it to only format to hh:mm?

format time problem
 

Thats brilliant thanks Bob. I have had a good read and implemented this. I
even managed to make a few changes specific to the way time is likely to be
input. I amazed myself!!!

I have one further question. When using that code if a user inputted the
time as 10:00 and actually used the colon then it throws up the incorrect
time entered error. Is there anyway of surpressing that in case the more
conscientious users input the time in its full and correct format?

Thanks for all your help Bob

"Bob Phillips" wrote:

See http://www.cpearson.com/excel/DateTimeEntry.htm

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Hi,

I have a column for enginners to enter the time an appointment starts. I
want this column to look like 10:00, 12:00, 14:00 and so on. Not those
exact
numbers, just that format. I went to format cells and all the time formats
include seconds or dates so I went to custom formats. On here I found
hh:mm
which is exactly what I need. The only problem is that if I simply enter
10
for instance meaning 10am (as many engineers will) for some reason the
time
remains at 00:00 and a date is added before it, usually a date in Jan
1900.
Is there anyway of forcing it to only format to hh:mm?

format time problem
 

Just edit the value before you parse it to remove : like this

Target.Value = CStr(Replace(Target.Value, ":", ""))

then the code that does the real work

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Thats brilliant thanks Bob. I have had a good read and implemented this. I
even managed to make a few changes specific to the way time is likely to
be
input. I amazed myself!!!

I have one further question. When using that code if a user inputted the
time as 10:00 and actually used the colon then it throws up the incorrect
time entered error. Is there anyway of surpressing that in case the more
conscientious users input the time in its full and correct format?

Thanks for all your help Bob

"Bob Phillips" wrote:

See http://www.cpearson.com/excel/DateTimeEntry.htm

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Hi,

I have a column for enginners to enter the time an appointment starts.
I
want this column to look like 10:00, 12:00, 14:00 and so on. Not those
exact
numbers, just that format. I went to format cells and all the time
formats
include seconds or dates so I went to custom formats. On here I found
hh:mm
which is exactly what I need. The only problem is that if I simply
enter
10
for instance meaning 10am (as many engineers will) for some reason the
time
remains at 00:00 and a date is added before it, usually a date in Jan
1900.
Is there anyway of forcing it to only format to hh:mm?

format time problem
 

Bob, again thank you for your time.

I hate to ask this as I do try hard to avoid being spoon fed from this forum
as it is always better to learn. I just cannot figure out where the code
string should go. I have read through the complete script a couple of times
and tried adding it in multiple places but just cannot get it to work,
everytime i enter a : i get the incorrect time error message.

I put it before the EnableEvents = False and ended up with a loop. Anywhere
after that seems to have no effect. Do I need any extra little bits of code
either side or anything like. If you could tell me the line that the
'replace' code goes after I would much appreciate it.

Thanks again Bob

"Bob Phillips" wrote:

Just edit the value before you parse it to remove : like this

Target.Value = CStr(Replace(Target.Value, ":", ""))

then the code that does the real work

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Thats brilliant thanks Bob. I have had a good read and implemented this. I
even managed to make a few changes specific to the way time is likely to
be
input. I amazed myself!!!

I have one further question. When using that code if a user inputted the
time as 10:00 and actually used the colon then it throws up the incorrect
time entered error. Is there anyway of surpressing that in case the more
conscientious users input the time in its full and correct format?

Thanks for all your help Bob

"Bob Phillips" wrote:

See http://www.cpearson.com/excel/DateTimeEntry.htm

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Hi,

I have a column for enginners to enter the time an appointment starts.
I
want this column to look like 10:00, 12:00, 14:00 and so on. Not those
exact
numbers, just that format. I went to format cells and all the time
formats
include seconds or dates so I went to custom formats. On here I found
hh:mm
which is exactly what I need. The only problem is that if I simply
enter
10
for instance meaning 10am (as many engineers will) for some reason the
time
remains at 00:00 and a date is added before it, usually a date in Jan
1900.
Is there anyway of forcing it to only format to hh:mm?

format time problem
 

Without seeing the code it is difficult for me to be specific, but I would
expect there to be some code that checks that the correct range has been
changed, and maybe some code to check for just one cell being changed, it
would go after those, before you do anything else with target.

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Bob, again thank you for your time.

I hate to ask this as I do try hard to avoid being spoon fed from this
forum
as it is always better to learn. I just cannot figure out where the code
string should go. I have read through the complete script a couple of
times
and tried adding it in multiple places but just cannot get it to work,
everytime i enter a : i get the incorrect time error message.

I put it before the EnableEvents = False and ended up with a loop.
Anywhere
after that seems to have no effect. Do I need any extra little bits of
code
either side or anything like. If you could tell me the line that the
'replace' code goes after I would much appreciate it.

Thanks again Bob

"Bob Phillips" wrote:

Just edit the value before you parse it to remove : like this

Target.Value = CStr(Replace(Target.Value, ":", ""))

then the code that does the real work

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Thats brilliant thanks Bob. I have had a good read and implemented
this. I
even managed to make a few changes specific to the way time is likely
to
be
input. I amazed myself!!!

I have one further question. When using that code if a user inputted
the
time as 10:00 and actually used the colon then it throws up the
incorrect
time entered error. Is there anyway of surpressing that in case the
more
conscientious users input the time in its full and correct format?

Thanks for all your help Bob

"Bob Phillips" wrote:

See http://www.cpearson.com/excel/DateTimeEntry.htm

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
Hi,

I have a column for enginners to enter the time an appointment
starts.
I
want this column to look like 10:00, 12:00, 14:00 and so on. Not
those
exact
numbers, just that format. I went to format cells and all the time
formats
include seconds or dates so I went to custom formats. On here I
found
hh:mm
which is exactly what I need. The only problem is that if I simply
enter
10
for instance meaning 10am (as many engineers will) for some reason
the
time
remains at 00:00 and a date is added before it, usually a date in
Jan
1900.
Is there anyway of forcing it to only format to hh:mm?