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

G

Gomer

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

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
 
D

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