using time data copied and pasted into excel from external source

[Ravi B]

I am trying to add time duration of song tracks together with information
that was copied and pasted into excel from iTunes. The song length is in
format 04:33 meaning 4 mins and 33 seconds. In trying to sum several song
lengths together it becomes clear that excel is reading this 04:33 as 4 hours
and 33 minutes which gives very peculiar results if you are looking for total
running times for various groups of songs. How do I get excel to recognise
this copied data as minutes and seconds rather than hours and minutes. In
trying to format the cells to minutes and seconds it changes the values, I do
not want this.

Please Help!

Thanks in Anticipation

 

[Pete_UK]

If your time values are in column D, for example, you can use a helper
column with this formula:

=D2/60

formatted as h:mm:ss, then copy down. You could fix the values then
copy/paste over the originals.

Alternatively, put 60 in an empty cell somewhere, select that cell and
click <copy>, then highlight the cells with the spurious times in and
right-click then Paste Special | Values (check) | Divide (check) | OK
then <enter>. Format the cells as h:mm:ss, and then delete the cell
containing 60.

Hope this helps.

Pete

 

[Dave Peterson]

put 60 in an empty cell
Edit|copy that cell
Select the range to fix
Edit|paste special|divide

You may want to reformat the cells to something like:
hh:mm:ss

Or you could just fix the cell with the total:
=sum(yourrangehere)/60

ps. You can use this format to show the total in minutes and seconds:
[mm]:ss
or
[hh]:mm:ss
to see the total in hours, minutes and seconds.

 

[John Moore]

Format the cells containing the times as hh:mm:ss ,,, set your times as
00:04:33 etc etc.

 

[Pete_UK]

If the data is imported or copy/pasted, then 04:33 will be taken by
Excel as meaning 04:33:00 rather than 00:04:33, no matter what the
formatting says.

Pete