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?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,749
Messages
5,513,164
Members
408,939
Latest member
jessica116

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top