Excel formats data incorrectly

sred

New Member
Joined
Aug 3, 2010
Messages
4
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Aug 3, 2010
Messages
4
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.
 

sred

New Member
Joined
Aug 3, 2010
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Aug 3, 2010
Messages
4
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,422
Messages
5,831,527
Members
430,074
Latest member
Francis101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top