Format Cells -> Time Format - pls Help!

refurbished

New Member
Joined
Oct 18, 2006
Messages
3
Hello everybody!
Can anyone help me on a little problem?

I'm importing this text:


00:01:00
00:01:50
00:32:11
00:59:22
01:04:22
08:17:11
19:23:50
58:34:22
59:49:22


First 2 numbers (mm) are minutes, then seconds and then hundredth of seconds. But I can't figure out a way to import this data proprely!

I want to format this column like this: mmm:ss,00 so minutes would NOT be transformed in hours and seconds would show their split seconds, those hundredth of seconds. Like this:


mmm:ss.00
-------------------------
000:01.00
000:01.50
000:32.11
000:59.22
001:04.22
008:17.11
019:23.50
058:34.22
059:49.22




I've tried eveything on the format cells help but I just can't figure it out! It's very frustanting! Can anyone take a minute to enlight me? Please!
Thanks in advance!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

refurbished

New Member
Joined
Oct 18, 2006
Messages
3
Thank you for reading, I just did what you wrote and the result is:

01:00.00
01:50.00
32:11.00
59:22.00
64:22.00
497:11.00
1163:50.00
3514:22.00
3589:22.00


Wrong! Because Excel formats my minutes as hours :( First tab are minutes! not hours. Last tab are hundreth of seconds and the middle tab is seconds. That's why I started a thread - I tried everything before and didn't figure it out.

Cheers!
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Oops - sorry, I didn't appreciate that you were getting these values in format 00:00:00 already (but as you said, with different meanings from the way Excel handles time).

You'll need to import these values as text in which case, and use a formula to convert such as:

Code:
=SUBSTITUTE(A1,":",".",2)+0

and format the formulas cell in the way I gave above.

Richard
 

refurbished

New Member
Joined
Oct 18, 2006
Messages
3
Thank you!

It works but only without that extra "+0" at the end of your formula.

Works just great without it, but I'm curios to ask: what's it for?


Best regards!


edit: hope you have an excelent week! as excelent as you just made my day! :)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
The +0 is there to coerce Excel to convert a text value (in a numerical format) to a numerical value (ie to which formatting can be applied, sums done etc). i needed this in my formula on my machine as otherwise Excel didn't recognise that I had a numeric value (ie it didn't apply my custom format to it, because it thought I had text). For whatever reason though, you seem to be able to do without it!

Or is it the case that now you simply have the values in a text format? You can check by seeing if they add up when you select a range of them (you should see a sum in your status bar towards the bottom right).

Richard

EDIT: It may well be that text values are perfectly adequate for your needs, in which case, I'm lad I could help! :biggrin:
 

Forum statistics

Threads
1,136,369
Messages
5,675,360
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top