Excel time format problem

[AussieSteve]

Currently trying to figure out a number format problem in Excel.
I download a CSV file that has times in the workbook that look like this (01:00:83). After I remove the parentheses it has this number 0.4262____ in the cell. Any number format that I try to apply to that cell would result in 1:01:23 a gain of 0.4? The table below is a snippet of the data in the worksheet, Any help in identifying the problem would be a great help.
Thanks Steve.

time		time
(01:19:30)		1:19:30
(01:12:06)		1:12:06
(01:12:14)		1:12:14
(01:00:83)		0.042627315 h:mm:ss
(00:57:85)		0.04056713
(00:57:28)		0:57:28
(01:00:57)		1:00:57
(01:01:86)		0.043356481
(00:58:46)		0:58:46
(01:03:52)		1:03:52
(00:56:78)		0.039791667

<tbody>
</tbody>

 

[Norie]

What do the last 2 digits represent?

If it's seconds then 83 seconds is 1 minute and 23 seconds and that's why you get 1:01:23 and not 1:00:83 when you format as time.

 

[AussieSteve]

Hi Norie.
And thanks for your reply, all of these times are in minutes, seconds, and hundredths of seconds, unless they haven't reached one minute, in which case it will be (00) no minutes just seconds and hundredths of seconds. Like I stated in my first part of my post I am not having any luck with a format, some of those numbers didn't transfer and some did without formatting. I am reading up on how to do this in VBA, but either way would be good to fix the problem at the moment.

 

[jtakw]

Hi,

If your values are minutes:seconds.hundredth of seconds

You'll need to Custom format the cells as

mm:ss.00

for it to display properly (Please notice that's a Dot/Period in front of 00 , Not Colon), increase the number of 0s (zeros) for the precision you need.

 

[AussieSteve]

Hi jtakw.
Thanks for your reply, typed in mm:ss.00 and got back 01:23.00 much to my dismay. Just re-checked the format in the custom format dialogue box and it is as I have typed it above in that cell. I have checked in Excel options-Advanced-When calculating this workbook, use 1904 date system is unchecked, should I have Set precision as displayed. only top and bottom check boxes are checked in this area.
thanks Steve.

 

[jtakw]

I was just letting you know that the correct format for minutes:seconds.hundredth of seconds

If the cells are formatted mm:ss.00, Then you enter in a time value, say 1:19.30 , then it Will display properly.

However, since you're trying to convert values you imported, assuming your imported values are all in the format you show in OP, you can use this formula, copied down ( Remember to format result cells mm:ss.00 )

If your imported values are different than what's shown in OP, please give various samples:

Book1
	D	E
1	(01:19:30)	01:19.30
2	(01:12:06)	01:12.06
3	(01:12:14)	01:12.14
4	(01:00:83)	01:00.83
5	(00:57:85)	00:57.85
6	(00:57:28)	00:57.28
7	(01:00:57)	01:00.57
8	(01:01:86)	01:01.86
9	(00:58:46)	00:58.46
10	(01:03:52)	01:03.52
11	(00:56:78)	00:56.78
12
13		^Format this Column
14		mm:ss.00
Sheet454

Cell Formulas
Range	Formula
E1	=SUBSTITUTE(MID(D1,2,LEN(D1)-2),":",".",2)+0

 

[AussieSteve]

Hi jtakw.
applied your formula, that copies over ok with no errors, but when it gets to the 4th row of numbers 01:00:83 becomes 1:01:23,Excel custom formats it to h:mm:ss,then I change it to mm:ss.00 moves to become 01:23:00. some rows transfer over ok and others do not like rows 4,5,8,11 in the data I originally sent. Between the 01:00:83 and 1:01:23 there is 4 one hundredths of a second to make it in accurate, this where the problem is?

 

[AussieSteve]

I had a look in some other forums including Microsoft, and found mostly it was a perception that needs to learnt about the way Excel handles dates and times, so after some reading and experimentation I am now ok with it. Thanks to everyone to take the time to answer my post.
Steve.

 

[jtakw]

You're welcome, glad you sorted it out.

 

[Rick Rothstein]

I had a look in some other forums including Microsoft, and found mostly it was a perception that needs to learnt about the way Excel handles dates and times, so after some reading and experimentation I am now ok with it. Thanks to everyone to take the time to answer my post.

Not sure what you did to resolve it, but for others reading this thread, here is how I would do it...

=0+("0:"&SUBSTITUTE(MID(D1,2,LEN(D1)-2),":",".",2))

Custom format the cell with mm:ss.00