Problem description: Steps to reproduce: 1.Insert text 02-01-2014 in cell A1; 2.Copy cell with formula =value(A1) to cell B1; 3.Format cell B1 as date; 4. Copy cell B1; 5. Paste only values (or only text, result is the same)to cell C1; 6. Copy result is a blank cell Current behavior: paste only value gives a blank cell Expected behavior: paste only value of the formula with data format 02-01-2014 or at least the value 41641 Operating System: Windows 7 Version: 4.1.4.2 release
Hi. Investigations under libO 4.2.0.1 rc1 on Win7 : - on my (english) system, the format of 02-01-2014 is "number", not "date" neither "text" ; explanation in Tools/Options/Language Settings/Language, set to "D/M/Y;D/M;D-M" : not "D-M-Y", so OK. Is it the same in your "language dependant" settings ? - I admit tha "number" is a questionnable choice, it should have been "text" ("number" only if the text is a well-formed number). - the function VALUE(x) waits a "text" argument (see online doc), so it rightly fails in "=value(A1)" in cell B1 containing "Err:502". Do you confirm ? - no place in LibO documentation suggests that a cell in error should contain in background the offending content, so the copy/paste from B1 to C1 had no chance to result in a "date" or "number" format. The copy/paste special (value only) rightly results in a blank cell. IMHO, the current (and only) bug resides in the bad automatic formatting of 02-01-2014.
Created attachment 91624 [details] Testcase Hi Dominique, thanks for your reply. I looked at my "language dependent" settings and they are "D-M-Y;D-M", corresponding to my regional settings (Portugal). My problem ocurred in a csv file with an account extract. Cell A1, with date ex.07-01-2014 was left alined and was not afected by changing number format, thus I suposed was text. Using the formula =value(A1) in cell B1 doesn't report "Err:502", instead, gives a value 41646. Formating B1 to date "DD-MM-AAAA" gives 07-01-2014 (=A1). When I copy de cell B1 (formated) and paste it to cell C1 with the option "paste only values", results in a blank cell. I send you a testcase.
(In reply to comment #2) > Formating B1 to date "DD-MM-AAAA" gives 07-01-2014 > (=A1). When I copy de cell B1 (formated) and paste it to cell C1 with the > option "paste only values", results in a blank cell. > I send you a testcase. May be because it is a date ??? Paste fine with paste special "Date and time".
Hi Gerard, Your solution works, but I think that isn't normal to copy a formula which value is visible and then paste it as a value to another cell resulting in a blank cell. At least a non formated value should appear...
I deepened my analysis. I was wrong when saying "on my (english) system, the format of 02-01-2014 is number" : I set "Tools/Options/Calc/View/Display value highlighting" and saw it was effectively a text. On your testfile.ods, cell B4 (containing =value(A4)) computes as "41641", but if I set another cell to the same formula, it computes to "Err:502". If I change "02-01-2014" to "02/01/2014", my cell computes to 41641. So I suppose that the "value()" function interprets its text argument through formats (like the auto-formatting when typing a cell content), and that the text "02-01-2014" in your cell A4 keeps the memory of an anterior interpretation with the portuguese "D-M-Y" date format... but I cannot see any place where this initial characteristic of "portuguese" appears. Further debugging needed to qualify the bug. PS: just to be sure : "copy/paste special (value only)" doesn't exist as such ; we have : - paste special, which opens a pop-up windows with around twenty fields, - or paste only, which opens a submenu with "Text/Number/Formula", Do you have the same on LibO 4.1.4.2 Portuguese ? In your initial Description : - "Paste only values to cell C1" : not a provided feature, - Paste only text to cell C1: rightly pastes nothing (= blanks the cell) when the copied cell is numeric (whichever format it shows : number, date, ...).
My LibO 4,1,4,2 Portuguese as the same paste options as the english version: - paste special , which opens a pop-up windows with around twenty fields, - or paste only, which opens a submenu with "Text/Number/Formula", When I said "copy/paste special (value only)" I meant “paste only/number”.
I agree with Gerard..vote for NOTABUG. Date is not a number, so Paste Only "date" currently not apllicable. Perhaps you would request an enhancement about that.
I am marking this as a duplicate of an enhancement request we have. Technically it's true, this is NOTABUG but the enhancement request being resolved would also avoid this confusion. *** This bug has been marked as a duplicate of bug 81346 ***