Quote:
Originally Posted by Ash87
Hi there,
So the national hockey league has a statistic known as Time on ice which is reported in minutes and seconds, on websites it is reported mm:ss. For example, one player might play for 12:23 or 12 minutes and 23 seconds. However, when I copy and paste these stats into excel, it think it as a time like 12:23 PM. How can I change this to 12 minutes and 23 seconds?
Thanks
Ash
|
Excel is quirky with time data, IMHO. Here is a workaround that might work for you:
1) Format cells A1,B1,C1,D1 to Text
2) Put the 12:23 in A1
3) Put this in B1: 0
4) Put this in C1: =LEFT(A1,2)
5) Put this in D1: =RIGHT(A1,2)
6) Format cell E1 to hh:mm:ss
7) Put this in E1: =TIME(B1,C1,D1)
You could skip step 3 then format cell E1 to mm:ss