ExcelBanter - TIME FORMATING

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - TIME FORMATING (https://www.excelbanter.com/excel-discussion-misc-queries/109211-time-formating.html)

I am getting data from a party for usage some services. But the data I am
getting is not in time format. This is something like given below:

00.18.00.00
00.10.30.05
00.12.58.01
00.07.03.05
00.10.09.24
00.10.39.13
00.13.30.24
00.10.33.14
00.10.51.02
00.12.31.23
00.11.50.00
00.09.18.03

how I can convert this to time format ie. 00:00:00.00

thanks in advance.

Hi

Try
=--(MID(A1,4,2)&":"&MID(A1,7,2)&":"&RIGHT(A1,2))
and copy down
Format the resulting cells as FormatCellsNumberCustomhh:mm:ss

After completing the above, copy the full range of results and Paste
SpecialValues to fix the data. The original column of data can then be
deleted.
--
Regards

Roger Govier

"shaji" wrote in message
...
I am getting data from a party for usage some services. But the data I
am
getting is not in time format. This is something like given below:

00.18.00.00
00.10.30.05
00.12.58.01
00.07.03.05
00.10.09.24
00.10.39.13
00.13.30.24
00.10.33.14
00.10.51.02
00.12.31.23
00.11.50.00
00.09.18.03

how I can convert this to time format ie. 00:00:00.00

thanks in advance.

=LEFT(A1,2)&":"&MID(A1,4,2)&":"&RIGHT(A1,5)

---------------
mama no teeth

"shaji" wrote:

I am getting data from a party for usage some services. But the data I am
getting is not in time format. This is something like given below:

00.18.00.00
00.10.30.05
00.12.58.01
00.07.03.05
00.10.09.24
00.10.39.13
00.13.30.24
00.10.33.14
00.10.51.02
00.12.31.23
00.11.50.00
00.09.18.03

how I can convert this to time format ie. 00:00:00.00

thanks in advance.