Hi klsu,

thanks for your report here.
I think this is covered also in another issue.
Looking at
 - bug 43442
 - bug 69750
 - bug 79116
Any preference?
Cheers,
Cor

I'll try this again...from the correct account and as plain text...

Yep, sounds like 3 other versions of the same problem. Unfortunately, 
Excel has it right. My preference is whatever will allow me to put 1 
button on the tool bar that will allow me to just past the results I see 
in the area copied without its format. As I said, I cannot think of a 
single use for being able to highlight an area showing numbers, text and 
dates and then copying only numbers or text or dates or everything but 
numbers or text or dates. If I could, I would need it so seldom that it 
would not be worth the the extra time I waste not being able to copy all 
three with one click, which I do very often.

On 07/10/2014 02:03 AM, bugzilla-daemon@freedesktop.org wrote:

Hi klsu,

thanks for your report here.
I think this is covered also in another issue.
Looking at
  -bug 43442  <https://bugs.freedesktop.org/show_bug.cgi?id=43442>
  -bug 69750  <https://bugs.freedesktop.org/show_bug.cgi?id=69750>
  -bug 79116  <https://bugs.freedesktop.org/show_bug.cgi?id=79116>
Any preference?
Cheers,
Cor

4.3.0.4 now has defaults ticked for only Text, Numbers and Date/Time making it so much easier to perform copy/paste without formats which means that Conditional Formatting is no longer disrupted.

Hi klsu,

Looking at comment #3 I suggest to set this one as resolved.
OK?

I've looked at my original post relative to LO Calc 4.4.0 Beta2 Daily installed on 2014-12-18, and it's better, but still not there. I still cannot think of a single use for a Paste command that does not paste the entire copied area, without a high risk of not copying something important from the copied area. Obviously this isn't a bug ('though I did find another one in the process of checking), since the behavior is intentional, but intentional behavior that can cause problems that are worse than the feature being added is beneficial should be retired. There are 2 Paste Special operations I use so often that it's faster to add them as buttons to the toolbar: Paste (displayed) Values, and Paste Formats. In LO Calc 4.4.0 Beta2, neither of these functions are available as single function buttons; I have to use Paste Special..., which takes a lot more time and is more prone to errors because of all the settings that must be set (I remove the Copy and Paste buttons entirely, because the keyboard shortcuts are faster).

Having Paste Special retain the last used settings is correct, however, if someone wants to copy only the results of a calculated area that displays dates, numbers, and Text, Paste Special must be used; meaning that if someone also regularly performs special Paste operations, they're forced to use of Paste Special, only because someone saw fit to add a feature of questionable use. Who selects an area they want to copy only the displayed results of, but only wants the numbers, dates (which are really numbers) or non-numbers copied? If they only wanted part of the area copied, they'd have selected only that part of the area.

The typical purpose of Paste Values is to be able to copy areas without formatting (and comments), either because the formatting in the destination area is desired, because the formatting is not compatible with the application into which the copy is being made, or because only the displayed values need to be retained, for whatever reason.

Example: add the Paste Value and Paste Text buttons to your toolbars.
in cell A3 enter 5
in cell A4 enter =1/9
in cell B2 enter =NOW()
in cell B3 enter =TODAY()
in cell B4 enter =A4*A3
in cell B5 enter =LEFT(B6,1)
in cell B6 enter =SUBSTITUTE(A4,"0",A3)
in cell B7 enter =A3&"th"
select and copy range B2:B7
in cell D2 click the Paste Values button
in cell E2 click the Paste Text button
in cell C2 click the Paste Value and then the Paste Text button (Why would anyone want to copy only the numbers or only the column and row labels of an area?) 

As an engineer, I have uses spreadsheets a lot for many years, and I still cannot think of a single situation in which any of these results would be acceptable or even useful, but many engineering and financial situations where they could cause serious problems. The above example is just that. In real life, Paste (displayed) Values is often used to save results of a very large area so that an incomplete paste operation might not be noticed until part of a building fails or an account doesn't balance.

Also treating dates separately from numbers and text is not justified in real life: dates are just specially formatted numbers. Just because a number happens to be formatted as text, doesn't mean it's not a number or has no value to the person using it. Even Text has value (or you couldn't sort text, and Paste Special... with Add checked would ignore text).

So how should this be treated? LO Calc would be enhanced if the Paste Value button actually pasted all values in the copied area, and Paste Text button were replaced with a Paste Formats button. In my opinion, the separation of Text, Numbers and Dates in Paste Special... adds work, increases the chance for errors, and adds no useful functionality to LO Calc.

@jay:

Wasn't this something that you were looking in, paste options?
thanks,
Cor


(In reply to klsu from comment #5)

> being added is beneficial should be retired. There are 2 Paste Special
> operations I use so often that it's faster to add them as buttons to the
> toolbar: Paste (displayed) Values, and Paste Formats.....

@Cor: As the paste special submenu was only added to writer, i was thinking to bring it to calc as well and combining the current 'Paste Special...' entry and 'Paste Only' submenu into a single submenu, like below.

Paste Special >
  Unformatted Paste
  Only Text
  Only Numbers
  Only Forumlas
  - separator -
  More Options...

Dear Bug Submitter,

This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information.

For more information about our NEEDINFO policy please read the wiki located here: 
https://wiki.documentfoundation.org/QA/FDO/NEEDINFO

If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed.


Thank you for helping us make LibreOffice even better for everyone!


Warm Regards,
QA Team

This NEEDINFO message was generated on: 2015-07-18

Not sure what additional information is needed on this issue. The last I checked, LO Calc had been modified so that the default for Paste Special has Numbers, Text and Date & time checked, which is fine as long as you don't want to Paste Special anything else. If you do, then you have to uncheck all three of those options because none of them does what it says it does alone:

Paste Special > Numbers really means "anything, result or constant, in the copied range that is a number and is not formatted as text (including dates/times) will be replaced by its value as a number, AND all Text and Date/Time values will be erased. That's not the same as paste values; pasting is not deleting.

Paste Special > Text really means "anything, result or constant, in the copied range that is text or formatted as text (excluding dates/times) will be replaced by its "value" as text, AND all Number and Date/Time values will be erased. Again, that's not the same as paste values; pasting is not deleting.

Paste Special > Dates & times really means "anything, result or constant, in the copied range that is a date and/or time (which is really a number used as a number with a text style of formatting) will be replaced by its "value" as a date/time formatted number, AND all other Number and Text values will be erased. Pasting is not deleting.

So the only information I see missing here is a common, real life example of a situation in which a normal user would ever want data deleted as part of a paste operation in an area they've copied with the intent of converting all formula results with actual values.

I won't waste any more time on this bug or suggestion or whatever you'd like to see it as; because as long as the MOD() and DATE() functions in LO Calc (and 3 other open source spreadsheets) can give random wrong answers, only Excel is safe for professional use.

(In reply to klsu from comment #9)
> Not sure what additional information is needed on this issue. The last I
> checked, LO Calc had been modified so that the default for Paste Special has
> Numbers, Text and Date & time checked, which is fine as long as you don't
> want to Paste Special anything else. If you do, then you have to uncheck all
> ...

There are three predefined options now.
And Adding a predefined set for all scenario's will create a nightmare in the dialog..

> I won't waste any more time on this bug or suggestion or whatever you'd like
> to see it as; because as long as the MOD() and DATE() functions in LO Calc
> (and 3 other open source spreadsheets) can give random wrong answers, only
> Excel is safe for professional use.

Do you have a clear reference, bug id's ?
(Apparently (see above) you didn't try LibreOffice for some time?)

thanks - Cor

I see the three predefined options now in LO Calc Version:
4.4.2.2
Build ID: 40m0(Build:2)
Locale: en_US
The first 2 appear to be what are required, and I am not asking for a predefined set for all scenario's; I am still wondering why there are still the three misleading paste special options:
Text
Numbers
Date & time
...which actually mean:
Paste text results as text and clear numbers and numbers formatted as dates.
Paste numerical results as numbers and clear text and numbers formatted as dates.
Paste numbers formatted as dates as dates and clear text and numbers.

I still haven't been provided an example of a reasonable use for these options. 

The DATE() bug is 87386 and MOD() bug was 87506, which may have later been combined with 87386, because the cause of both may be the same. The example I gave when I reported 87386 creates a result that is wrong by one month (also in 4.4.2.2). Imagine tracking deliveries, financial transactions or medical procedures in a spreadsheet that randomly moved dates up by one month. I don't use LibreOffice Calc much anymore, because I use DATE() and MOD() for many things and can't accept the risk.

Hi klsu,

Does pasting using the 'Unformatted Text' option in the paste toolbar button's drop down menu part work according to how you like it?

(In reply to klsu from comment #11)

> The DATE() bug is 87386 and MOD() bug was 87506, which may have later been
> combined with 87386, because the cause of both may be the same. The example

Thanks for pointing to those.
Reading the much more informed comments from others that I would be able to give, it's clear that I'm not able to make you happy hear. Sorry for that.

> MOD() for many things and can't accept the risk.

Proper and save ways to use the spreadsheet have been provided at your service.

Cheers,
Cor

(In reply to Cor Nouws from comment #13)
> Thanks for pointing to those.
> Reading the much more informed comments from others that I would be able to
> give, it's clear that I'm not able to make you happy hear. Sorry for that.
...
> Proper and safe ways to use the spreadsheet have been provided at your
> service.

As you can see in the examples I provided, I'm able to figure out work-arounds for the problems with with DATE() & MOD(). However, because the underlying cause of the problem has not been identified, there is no way of knowing when the problem will occur, or if it will occur with something else. I accidentally used a situation that got a wrong answer and was fortunate to notice it. When I experimented to find out the extent of the problem, I found that it was not predictable. As I think I said somewhere in that bug report, if someone loses a lot of money or their life because of it, the bug might finally get fixed...

(In reply to Yousuf (Jay) Philips from comment #12)
> Hi klsu,
> 
> Does pasting using the 'Unformatted Text' option in the paste toolbar
> button's drop down menu part work according to how you like it?

I had to add the Paste button back to my toolbars; I use the context menu or keyboard shortcut for pasting.

I think What it does is useful, but Paste "Unformatted text" isn't really what it's doing, because a cell that contains a date (which is really a specially formatted number), is pasted formatted as a date. Perhaps "Formulas as values" would be a more accurate description.

I'm not sure what "Formatted text [RTF]" is doing. When I copy the same range that I used to test "Unformatted text" and Paste "Formatted text [RTF]", the result is the same as with Paste "Unformatted text", but the format is changed (original font 10 point Liberation Sans red, pasted font 12 point [undefined] black). I'm not sure why the options in the button pull-down are nowhere in the "Edit" > "Paste Special..." dialog, but because of the [RTF] and the import dialog, I think these paste options are intended for copying from other documents into LibreOffice Calc.

(In reply to klsu from comment #15)
> I had to add the Paste button back to my toolbars; I use the context menu or
> keyboard shortcut for pasting.
> 
> I think What it does is useful, but Paste "Unformatted text" isn't really
> what it's doing, because a cell that contains a date (which is really a
> specially formatted number), is pasted formatted as a date. Perhaps
> "Formulas as values" would be a more accurate description.

So if i get you correctly, you want the standard paste that is available through Ctrl+V and does so without text formatting, rather than doing regular paste and then right-click 'Clear Formatting' and then reapplying the cell number formats.

> I'm not sure what "Formatted text [RTF]" is doing. When I copy the same
> range that I used to test "Unformatted text" and Paste "Formatted text
> [RTF]", the result is the same as with Paste "Unformatted text", but the
> format is changed (original font 10 point Liberation Sans red, pasted font
> 12 point [undefined] black).

Yes i had noticed the same thing and submitted it as bug 93362.

> I'm not sure why the options in the button
> pull-down are nowhere in the "Edit" > "Paste Special..." dialog, but because
> of the [RTF] and the import dialog, I think these paste options are intended
> for copying from other documents into LibreOffice Calc.

There are two paste special dialogs that open through Edit > Paste Special, one which shows the same options as in the paste button in the toolbar and the second that appears when you copy and paste text in calc.

(In reply to Yousuf (Jay) Philips from comment #16)
> (In reply to klsu from comment #15)
> So if i get you correctly, you want the standard paste that is available
> through Ctrl+V and does so without text formatting, rather than doing
> regular paste and then right-click 'Clear Formatting' and then reapplying
> the cell number formats.

I'm not sure you understand me. My concern is for copying and pasting from a range in a LibreOffice Calc spreadsheet to the same range (to convert all formulas to values) or to a different range (to copy all values, converting all formulas to values in the process). The problem is with the three options in the Paste Special dialog that applies to copying within a spreadsheet only:
Text
Numbers
Date & time
Unless they're all checked, they all delete the values in any cell of the type that is not checked. Paste means paste, not delete; and no-one has yet been able to give me a real world example of a valid use for such a paste operation. The only use I have ever had for pasting values is to convert formulas in a range to values or to copy all the values and formulas in a range to values in another range. There now a button for this at the top left of the Paste Special dialog. I don't understand why a button was added instead of just replacing those three items that aren't useful unless all three are checked, with one item (Paste Special > Values).

It now appears to me that the button with the paste options you are asking about shouldn't be active if the range being copied is in a LibreOffice Calc spreadsheet (see comment below). When I copy and paste these three lines:
This is a test
4526
8/12/15
...from a LibreOffice Writer document to a Calc spreadsheet range formatted General, using the paste "Unformatted text" option, it copies the first line as text (left justified), the second line as a number (right justified and recognized as a number by a formula), and the third line as a number (recognized as a number by a formula) and formatted as text (but left justified instead of right justified as it would be if I had typed in the date in Calc - so I guess that's a minor bug).

> > I'm not sure why the options in the button
> > pull-down are nowhere in the "Edit" > "Paste Special..." dialog, but because
> > of the [RTF] and the import dialog, I think these paste options are intended
> > for copying from other documents into LibreOffice Calc.
> 
> There are two paste special dialogs that open through Edit > Paste Special,
> one which shows the same options as in the paste button in the toolbar and
> the second that appears when you copy and paste text in calc.

I see now that the second Paste Special dialog is displayed from the menu, only when I copy from an external document (e.g., LibreOffice Writer), not a range in a LibreOffice Calc spreadsheet. If that means the Paste Button with those other options is supposed to be only for pasting from an external document, shouldn't it be inactive when the range being copied is within a LibreOffice Calc document?