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.