Convert text to time value

A

Andy

I have a series of time values in a 'General' format. They are of the
type:

184525

Which is 18:45:25 or 6:45:25 pm.

A time which is am would be of the type:

12345

Which is 1:23:45 am.

Is there a way to convert those 'General' values to an Excel serial so
that I can figure out the difference between two times? I've seen a
bunch of examples on the net, but none of those that I have found deal
with this format that I can tell.

Thanks.
 
S

Sandy Mann

If the entries are always going to be5 or 6 numbers then:


=--LEFT(A1,LEN(A1)-4)/24+MID(A1,LEN(A1)-3,2)/24/60+RIGHT(A1,2)/24/60/60

in a cell formatted as time will give what you want but note that I am not
sayong that is is a good solution, (or way of entering the data!)

HTH

Sandy
 
D

Dave Peterson

I had to add \'s:

=--TEXT(A1,"00\:00\:00")

and another (kind of selfdocumenting formula):

=TIMEVALUE(TEXT(A1,"00\:00\:00"))

Both have to be formatted as time.
hh:mm:ss
??
 
F

Frank Kabel

Hi Dave
interesting. In my German version I could use it without the '/'.
Thanks for the follow-up!
 
Top