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.

simple format cells as Number with custom 0000 format.
This will ensure that 0600 appears as 0600 and not 600

Steve


On Fri, 01 Dec 2006 10:13:00 -0000, 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.

Hi

Format the column as text


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"SpencerMC" wrote in message
...
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.

SpencerMC

Proceed your entry with an apostrophy. " ' " (Without the quotes) You will
see it in the formula bar but not on your sheet nor in print.

Mike Rogers


"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.

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.

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.

Hello, I wanting pretty much the same thing, however is there any way to
enter whole numbers ie...2300 and a formula automatically convert it to 23:00
where it will add the : . The problem is I have a worksheet that has approx
600 cells with time frame like 2300, however I am exporting it into another
program and it has to read it as 23:00, so without correcting every 600
entry, i was hoping to be able to highlight the entire column and apply a
format....any ideas

"Arvi Laanemets" wrote:

Hi

Format the column as text


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"SpencerMC" wrote in message
...
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.

I already gave you the answer to your question, you would need VBA to do
this and I gave you the link with the code in question


--


Regards,


Peo Sjoblom


"jshehan" wrote in message
...
Hello, I wanting pretty much the same thing, however is there any way to
enter whole numbers ie...2300 and a formula automatically convert it to
23:00
where it will add the : . The problem is I have a worksheet that has
approx
600 cells with time frame like 2300, however I am exporting it into
another
program and it has to read it as 23:00, so without correcting every 600
entry, i was hoping to be able to highlight the entire column and apply a
format....any ideas

"Arvi Laanemets" wrote:

Hi

Format the column as text


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"SpencerMC" wrote in message
...
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.

Not without some event code or a helper cell with a formula.

See Chip Pearson's site for code.

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


Gord Dibben MS Excel MVP

On Fri, 9 May 2008 08:30:02 -0700, jshehan
wrote:

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????

Oops.

Missed your post Peo so sent a duplicate.


Gord

On Fri, 9 May 2008 08:40:49 -0700, "Peo Sjoblom" wrote:

Using VBA

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

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.

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.

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.

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.