Time Formats

[AUTO]

Hi Folks,

I've imported a file from the web and it has times formated thus "06.45" (without the quotes). How can I convert it to a proper time format e.g. 06:45 in order to work with them.

Thanks.

 

[xld]

Do a Find & Replace (ctrl-H), and replace . with :

 

[AUTO]

Nice idea xld but it just screws up the times.

I need something that will give me a proper time which I can manipulate.

(I need to do some calculations with the resulting times)

Thanks anyway

 

[Hui]

Assuming your data is in Column A

put this in B2

=TIME(LEFT(A2,FIND(".",A2-1)),RIGHT(A2,2),0)

copy down

Now Copy column B and Paste as Values

Delete Column A

 

[xld]

Screws it up, in what way? Does it mess the numbers, or just leave it as text?

 

[xld]

Another way if you are happy with a calculated column


=--TEXT(SUBSTITUTE(A2,".",":"),"hh:mm")


and format as hh:mm

 

[AUTO]

Thanks hui & xld that's got it sorted.