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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,515
Messages
6,131,111
Members
449,620
Latest member
MatXm

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
Back
Top