How do I easily format a column to convert input of 120307 to 12:.
 

I'm trying to reduce the amount of keystrokes a customer needs to enter time
data. Essentially, they want to type only the numbers of an entry of
hh:mm:ss. When using a format built into Excel (non-custom), the system
turns the data into DD/MM/YY hh:mm:ss, coming up with an incorrect hour ,
minute, second conversion. any ideas?

Preformat the cells as text prior to entry. In fact, the
customer only needs 7 keystrokes (6 digits + <Enter):

A1: hhmmss

Then, to convert to a time that Excel understands, use:

=--REPLACE(REPLACE(A1,3,,":"),6,,":")

and format the formula cell as time.

HTH
Jason
Atlanta, GA

-----Original Message-----
I'm trying to reduce the amount of keystrokes a customer
needs to enter time
data. Essentially, they want to type only the numbers
of an entry of
hh:mm:ss. When using a format built into Excel (non-
custom), the system
turns the data into DD/MM/YY hh:mm:ss, coming up with
an incorrect hour ,
minute, second conversion. any ideas?
.

Gomer

See Chip Pearson's site for code for quick date/time entry.

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

Also Rob de Bruin's site for an add-in.

http://www.rondebruin.nl/qde.htm


Gord Dibben Excel MVP

On Wed, 9 Feb 2005 09:03:05 -0800, "Gomer"
wrote:

I'm trying to reduce the amount of keystrokes a customer needs to enter time
data. Essentially, they want to type only the numbers of an entry of
hh:mm:ss. When using a format built into Excel (non-custom), the system
turns the data into DD/MM/YY hh:mm:ss, coming up with an incorrect hour ,
minute, second conversion. any ideas?

Another version that works with a helper cell (like Jason's):

=--TEXT(--A1,"00\:00\:00")
(with A1 formatted as text, entering all 6 digits)
(still format it as time.)

Gomer wrote:

I'm trying to reduce the amount of keystrokes a customer needs to enter time
data. Essentially, they want to type only the numbers of an entry of
hh:mm:ss. When using a format built into Excel (non-custom), the system
turns the data into DD/MM/YY hh:mm:ss, coming up with an incorrect hour ,
minute, second conversion. any ideas?

--

Dave Peterson