Excel Adding Time

[yuvala]

Hi Everyone,
I'm having trouble with formatting the time in Excel 2010 and that's why I cannot add the time that are in a certain column.
The original times before I paste them into excel are (in minutes:seconds) :

36:51
0:00
17:12
0:08
0:00
0:00
4:18
2:42
0:00
71:07
0:06
0:50
0:08
0:00
0:57
0:12
3:26
0:00
1:08

When pasted into excel it looks like this:

36:51:00
0:00
17:12
0:08
0:00
0:00
4:18
2:42
0:00
71:07:00
0:06
0:50
0:08
0:00
0:57
0:12
3:26
0:00
1:08

It basically treats the minutes as seconds (but only in certain entries - highlighted in red)
I need excel to display it as h:mm:ss but as you can see above, it thinks the 36 minutes are 36 hours, and I don't know why it added ":00" at the end

I have tried everything but couldn't get it to work, any help would be greatly appreciated.

I need this urgently

Thanks in advance,
Yuval

 

[adam087]

It's treating them all as hh:mm, you can check this by altering the time format (CTRL+1 to open the Cell Format dialog box). Change the format to mm:ss to show the data as you want but this won't solve the factthe Excel THINKS they're hh:mm.

I'm afraid I can't think of a way of retaining this on pasting, but you could convert them from hh:mm to mm:ss simply by dividing by 60.

You could either do this in a seperate column, or you could type 60 in a blank cell, copy the cell, then select the column of times and choose Paste Special -> Operation -> Divide.

Does that work for you?

Regards
Adam