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

[Guest]

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?

 

[Jason Morin]

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

 

[Gord Dibben]

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

 

[Dave Peterson]

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