Excel formats data incorrectly

[sred]

Hi

I am exporting data from another application (telephone usage stats) into Excel. The data I export is in mm:ss, i.e. 07:16 however once its pasted into Excel it seems to be reformatted by Excel into 07:16:00. So Excel is converting the minutes into hours.

I want to be able to show this in the format mm:ss but if I do that now Excel shows me 16:00.

Any way around this?

 

[Richard Schollar]

Hi

After the import, you could just divide this column by 60 (eg using PasteSpecial>Divide) which will convert your hours/mins to mins/secs and format as required.

 

[sred]

Hi

Thanks for the quick reply.

Dont think that will work as one thing I forgot to mention was the report I am exporting from has other data in it too. For example some columns are just plain numbers, i.e. amount of calls received 40, 76 etc. Other columns are total time spent on phone (in hh:mm:ss) and these export correctly. It just seems to be certain columns (those in mm:ss) that Excel adds the :00 to the end of so its only these columns I want to amend.

I hope that makes sense.

 

[Richard Schollar]

So only do the divide on the columns that you need to?

 

[sred]

Hi Richard

Sorry to appear a bit daft (i'm not great with Excel as you can see), but i have to export the entire report from the other application, I can't export only certain columns from it so the whole thing gets exported and pasted as a chunk. Once its pasted into Excel can i just do what you suggested on certain columns? How would I do that?

 

[Richard Schollar]

When you have your report data in an Excel sheet, just go and type 60 into a blank cell somewhere (can be anywhere - just don't type over any existing data!). Then, copy this cell (with Edit>Copy or right-click>copy). Then, assuming your times are in column C, select all of column C by clicking on the column button at the top of the Excel grid. then go Edit>PasteSpecial>select Values and Divide (under operations)>OK. This should convert all numeric values in this column from hh:mm to mm:ss (although the format won't change so will still be displayed in hh:mm:ss but 07:16:00 will be converted to 00:07:16).

Make sense?

 

[sred]

You are an absolute star, thank you.

You wont believe how long I have been typing those numbers in manually because i couldn't figure it out.