Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time format problem | Excel Worksheet Functions | |||
Date/Time Format Problem | Excel Discussion (Misc queries) | |||
Time format problem | Excel Discussion (Misc queries) | |||
A Time Format Problem | New Users to Excel | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |