Time Formatting issue

[josephwalls]

I have a big report of elapsed times that show up as "1:34". I want to have the data be seen by excel as mm:ss but instead it shows up as the time of day (e.g. 1:34 = 1:34:00 a.m.). I am so lost I would appreciate any help. I need something that I can apply to a column of times approximately 200 rows long that converts them over to the mm:ss format so I can get an accurate mean of the data. Any assistance is greatly appreciated.
To duplicate what I've got going on just type the following into a cell "4 Mins 27 Secs", which is what I was given when I exported data to a csv file from our database. I then was instructed by our IT to just use Edit > Find/Replace > and replace " Mins " with ":" and " Secs" with [leave field blank]. Spaces are important. I then get 4:27 displaying in my field, the format is time. If I format the cell as [mm]:ss I get 267:00 in my field but the formula bar still displays 4:27:00 AM.

I've also tried to copy and paste special with only values but 2:26 becomes
"0.101388889" and still gets seen as 2:26:00 AM when I am just trying to get excel to see it as 2 minutes and 26 seconds. These are racing times which I hope to get averages and mean averages for but I am unable to with the current issue.

Thank you so much for all of your help!

 

[Andrew Poulsom]

Welcome to MrExcel.

Put 60 in a spare cell and copy it. Select your times, right click, choose Paste Special, check Values and Divide and click OK.

I think IT should have told you to save as 00:4:27.

 

[josephwalls]

Welcome to MrExcel.

Put 60 in a spare cell and copy it. Select your times, right click, choose Paste Special, check Values and Divide and click OK.

I think IT should have told you to save as 00:4:27.

When I perform this "1:29" becomes "0:01". I know I am making this more complicated than it should be. Basically I have a list of times from swimmers that were exported to .csv. The swimmer did a race in 1 minute and 29 seconds and the .csv file shows "1 Mins 29 Secs" as text. I just want excel to see the time as 1:29 where [1] is seen as minutes and [29] is seen as seconds so that I can perform averages and mean averages, etc. That's my issue in a nutshell.

Thanks for the help!

 

[Andrew Poulsom]

Just format the cell as mm:ss (or hh:mm:ss).

 

[josephwalls]

So I am able to format as hh:mm:ss and then copy 60 from a new cell and paste special, value, divide to come up with the correct view in minutes and seconds. When I click on the cells they show format as Time and under formula still display as time of day. Maybe this is always the case but all of my math appears to be working correctly for averages and mean averages.

Thank you so much for your help!

 

[Andrew Poulsom]

In Excel times are just decimals of a day formatted to display as times. So, in terms of fractions, one hour is 1/24, one minute is 1/24/60 and one second is 1/24/60/60. hence to convert hours to minutes you can divide by 60.