Number format not applied to pasted data

Hello all,

I have a thoroughly outdated system to keep a log of call centre stats. In order to facilitate a marginally easier workflow, I have rearranged a few things so that a user simply has to copy and paste data from reports into the worksheet.

The worksheet is set up with all the number formats like time and percentage which are extremely important in totaling and averaging hours and availabilities, however the pasted data doesn't match the destination format and it therefore affects the total.

I have tried many different special pasting options to no avail.

The only way i seem to be able to do it is to go into each line and retype my percentages (as 100 becomes 10000% ) and click the tick next to the formula bar so 00:00:16 is calculated as a quantity of time instead of text. With 30 people and 15 stats each day that exercise becomes a little bit tedious and counterproductive.

Can anyone help me please?

Hi, have you tried formatting the cell as percentage/hh:mm:ss then copy/paste special as value?

Originally Posted by alvin-chung

Hi, have you tried formatting the cell as percentage/hh:mm:ss then copy/paste special as value?

Thanks Alvin,

I have tried this, sadly applying the appropriate number format to existing does exactly the same thing... which is nothing for the time and multiplies % by 100 before adding the symbol.

Could you share some sample records for the report that you want to copy/paste to spreadsheet so that members can try simulate and assist you?

copy from.xls paste into.xlsx

Here are excerpts of the files I'm working with.

Try manual workaround by copy data from "copy from.xls" and paste to notepad, then copy from notepad to "paste into.xlsx".
This shall work for time format but not for percentage, possible to generate the report to show 73.1% or 0.731 instead of 73.1?

Thanks Alvin,

that works... but really??? REALLY? come on Microsoft it's 2014!!! There must be an easier way. I need to set this up so anyone can use it and it's bad enough trying to write processes for this thing as it is without having to add an extra program and copy/paste step.

Can anyone let me help me out? I've looked in settings and all kinds of things i've changed the format on one cell and tride to fil subsequnet cells with format only, none of it works. surely I'm just missing something.

Yeah, and that's what I've been doing for a workaround in the past...
Btw I've just tried out something interesting, see if this helps

1. copy range of data as usual
2. use paste special and check two options - "values" and "add"

ps: as highlighted earlier, the source of the percentage needs to be something like 73.1% or 0.731



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

You're welcome, thank you for your feedback and I've learned new trick for myself too



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin