Not showing time correctly

[krypton80]

Hi all
I need help with times in excel 2010
I exported a list of mp3 durations, but the column shows minutes as hours.
For example, mp3 duration: 7 minutes and 30 seconds
in the csv exported files shows as 7:30:00

Plus, those mp3 with duration over 24 minutes, show also wrong
For example mp3 duration 27 minutes and 15 seconds
in the csv exported file shows as 01/01/1900 3:15:00

I would like them to show like this:
0:07:30
and
0:27:15

I have uploaded csv file if you want to take a look (42 KB)
Zippyshare.com - time.csv

Thanks in advance

 

[pjmorris]

Welcome to the forum, I hope you find what you need.

One solution is to enter 60 in a blank cell - then copy it - then highlight all the affected cells and select Paste Special, then in the Paste Special dialogue box click the 'Divide' radio button, Click OK. All imported values will be divided by 60. You will probably need to reformat the column to get it to show time, I would use a custom format of mm:ss.

Hope this helps.

 

[oldbrewer]

I am not prepared to download files - copy some of it and paste it on here

workround is multiplying by 60 - also format them as numbers to 3 decimal places and see what they show

 

[krypton80]

I am not prepared to download files - copy some of it and paste it on here

workround is multiplying by 60 - also format them as numbers to 3 decimal places and see what they show

25:15:00

23:31

23:48

23:55

25:47:00

23:09

26:47:00

26:49:00

25:53:00

24:15:00

24:56:00

25:58:00

26:23:00

26:43:00

27:36:00

2:13

1:51

2:39 I don´t know how to insert the csv here sorry

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[VoG]

Cross posted Not showing time correctly

 

[krypton80]

It worked by dividing cells /60
Then custom format to h:mm:ss

Thanks for your help, and prompt replies

 

[pjmorris]

I am not prepared to download files - copy some of it and paste it on here

workround is multiplying by 60 - also format them as numbers to 3 decimal places and see what they show

Oldbrewer, please excuse the complaint, but if you're going to object to a new forum members approach (ie uploading a file) that you do so politely and that when providing advice you make sure it works (even more important if you've previously objected to something!). 07:30:00 * 60 = 18.75!

Regards

 

[oldbrewer]

Peter = Email and forum posts tend to be as brief as possible. The full version would have been - I am not prepared to download files on this forum because of the risk, however slight, of viruses, malware, ransomware. You got me bang to rights on the 07:30:00 I should have said divide! Notice the exclamation mark. It is like the one you used at the end of your post.!

 

[pjmorris]

fair comment.

 

[oldbrewer]

friends again, then !