Convert time formats 9.30 to 09:30

[colmmc]

I have a list of times in number format

9.30
9.35
10.50 etc

There's hundreds of them in various spreadsheets I have. I need them to be formatted as time so I can do time calculations on them.

Formatting the above examples directly as time I get

07:12:00
08:24:00
12:00:00

If I convert to text and replace the . with : I get

09:03
09:35
10:05


Now, I can probably write a macro to change all the 03s to 30s etc

But I'm wondering is there an easier method of formatting?

Thanks

Colm

 

[lexxie2013]

Hi. I'm sure there are easier ways, but in another column, you could put something like:

=TIME(INT(A1),MOD(A1,INT(A1))*100,0)

 

[colmmc]

Well that works for the examples I have given. I think it should probably work for everything I have. I'm not familiar with some of those functions so I've learned something else.

Thanks a million.

Colm