using time data copied and pasted into excel from external source
 

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
--
Ravi B

using time data copied and pasted into excel from external source
 

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

On Feb 9, 2:03*pm, Ravi B wrote:
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
--
Ravi B

using time data copied and pasted into excel from external source
 

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.



Ravi B wrote:

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
--
Ravi B

--

Dave Peterson

using time data copied and pasted into excel from external sou
 

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

"Pete_UK" wrote:

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

On Feb 9, 2:03 pm, Ravi B wrote:
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
--
Ravi B

using time data copied and pasted into excel from external sou
 

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

On Feb 9, 2:27*pm, John Moore
wrote:
Format the cells containing the times as hh:mm:ss ,,, set your times as
00:04:33 etc etc.



"Pete_UK" wrote:
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

On Feb 9, 2:03 pm, Ravi B wrote:
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
--
Ravi B- Hide quoted text -

- Show quoted text -