Did you paste Chip's code into the sheet module by right-click on the sheet tab
and "View Code" to open the sheet module?

Have you adjusted the range "A1:A10" to suit your range?

Did you use the proper code?

There are two sets of code on Chip's page.......one for entering dates and one
for times.


Gord

On Fri, 9 May 2008 12:31:02 -0700, jshehan
wrote:

Gord, Thanks for hanging in there with me. I tried going to formatcustom
and typing in my formula hh:mm because I do not need the seconds, and when I
do that in the cell it shows up as 00:00 no mater what you type in however at
the top on your formula bar it correctly shows what you typed in????

"Gord Dibben" wrote:

You will have to make a small change in the code near the end of code.

End Select
.Value = TimeValue(TimeStr)
.NumberFormat = "hh:mm:ss" 'add this line
End If

Or just pre-format the cells to Custom hh:mm:ss or the built-in Time format of

37:30:55


Gord


On Fri, 9 May 2008 11:31:12 -0700, jshehan
wrote:

sorry about the double posting...I am a little bit of a beginner on these
codes, still trying to figure out your web page. Will your codes
automatically insert colons when only typing in whole numbers like 2300 to
be displayed like 23:00

"Peo Sjoblom" wrote:

Using VBA

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

--


Regards,


Peo Sjoblom


"jshehan" wrote in message
...
I am having some what the same problems, however I am wanting the cell to
display 24hr time as ie...13:30 with the colons, and without actually
keying
in the colon when entering in the numbers as 1330. Is there any way that
it
will automatically insert the colons without keying them in????
"SpencerMC" wrote:

I have a column in my worksheet that users enter their start time in 24hr
clock (military time) e.g. 1830 or 0700

If I format the column using any of the time formats, I get various
results
since Excel treats the entry as a value and converts this into a time
(never
really understood this).

However, I don't need to do any calucalution with these times; it's
effectively just there as text. So when users enter 1830 it stays as
1830. So
far so good. However if they enter something with a preceeding 0, such as
0630, Excel removes the first zero and it becomes 630. In a list of 24hr
times this looks odd and will confuse people.

So any suggestions? I just want users to enter 24hr times and for them to
be
formated as such (as text or otherwise!) Cheers.