Description: Sample file attached. If text signifying a time (17:21) is copied and pasted special into a cell with either a time or number format then the pasted data becomes 0.7229166666*. If the same clipboard contents are simply pasted into non-formatted cells then it pastes the format which is automatically assigned by the editor to the original cells. From sample:- Observe the values in A7:A9 Observe the format of C7:C9 Observe the format of E7:E9 If you wish to test the reality then simply:- format say G7:G9 and H7:H9 in identical formats to C7:C9 & E7:E9 - NOTE: E7:E9 were originally formatted as text but paste reformats them according to its own autoformat criteria Copy A7:A9 PasteSpecial to G7:G9 PasteSpecial to H7:H9 This issue became apparent after pasting a simple text message stating 17:21 - which was a bus departure time - into a spreadsheet register of SMSs. Message dates and times were correctly interpreted but the conversion of 17:21 in a predefined text field to 0,72291666* was a bit of a surprise. Steps to Reproduce: If you wish to test the reality then simply:- format say G7:G9 and H7:H9 in identical formats to C7:C9 & E7:E9 - NOTE: E7:E9 were originally formatted as text but paste reformats them according to its own autoformat criteria Copy A7:A9 PasteSpecial to G7:G9 PasteSpecial to H7:H9 Actual Results: Original text first converted to a time format in the opening cells (A7:A9) and then reformatted to decimal formats in the paste special cells C8:C9) and autoformatted in paste cells Expected Results: In old money WYSIWYG BUT in many - NOT ALL - circumstances the autoformatting to realistic date and time formats would not be amiss. It may be considered that C9 should indeed be a decimal representation but arguably, only when the user had defined the source cell as a number, NOT when it was originally defined as text. I feel it's potentially a catch 22 situation. Is the anticipation of the time formats generally more acceptable than a true representation of the underlying data? Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 7.0.4.2 (x64) Build ID: dcf040e67528d9187c66b2379df5ea4407429775 CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 169940 [details] Simple .ods with examples
I should also mention that the original data - 17:21 - was pasted in a batch of 250 records with [ALT][CTRL][SHFT]"V" to force the copying of unformatted text into all the cells of the target sheet.
(In reply to Colin from comment #0) > Copy A7:A9 > PasteSpecial to G7:G9 > PasteSpecial to H7:H9 <<<<<<<<<<<<<<<<< Should of course say Paste to H7:H9 as it's supposed to be a comparison between paste and pastespecial
Have you seen that 17:21 it's a text not a true time in the spreadsheet. Use Menu/View/Value Highlighting Text is in black, numeric/date-time values in blue.
(In reply to m.a.riosv from comment #4) > Have you seen that 17:21 it's a text not a true time in the spreadsheet. > > Use Menu/View/Value Highlighting > > Text is in black, numeric/date-time values in blue. Precisely, It was text in the SMS, it was text in the SMSBackup XLS file in Dropbox, it was text in the view XML file at https://synctech.com.au/sms-backup-restore/view-backup/ using Firefox and it appeared as a formatted time when pasted special to the Lo Calc - see attached screen grab and compare it with the cut & paste herewith from the first stage of my processing; Sent 2021-02-17 2:35:43 PM Bus? Received 2021-02-17 2:36:00 PM 05:21 PM Sent 2021-02-17 2:36:43 PM Food? Note: As can be seen in the attached screen grab, the entire date & Time is one text string which I then chop and rationalise. The subsequent treatment in the sample calc can be verified as "unexpected" and "inconsistent". It's a two-stage operation. I select and copy the browser screen and then ALT CTRL SHFT V to the LO Calc which simply forces unformatted - NO dialogue box * to the first sheet and that is the representation above where the source 17:21 is autoformatted 05:21PM. I then manipulate some of the data but NEVER the text message which is then simply selected and copied with CTRL SHFT V to the destination and the dialogue box is confirmed "text, numbers, date and time". All the rationalised dates and times present as formatted but the 17:21 or 05:21PM which is sourced from a "numerically formatted? text" cell becomes decimal 0,72291666* within the target cell. It was only noticed by chance and I don't really conceive of vetting 7500 lines on the calc to ascertain if perhaps 10-20 could contain the same anomaly
Created attachment 169945 [details] Screen Grab of the source text format
Perhaps I should mention that the message text is the only data not manipulated in my process. I take the other data elements and convert them to real dates and times. evaluate the sender and recipient, and then select seven of the twelve columns. The paste special unformatted text is defined as "ignore blanks". The destination is a large autofiltered array from which I can "mix n' match" whatever I choose.
Perhaps I should also clarify that if you hover your mouse over A8 or A9 it is quite clear that I typed 17:21 everywhere and LO calc autoformatted but when the Col A source is cut and pasted to Col C & E the format is again autochanged from the time representations to decimal 0,7229166667 which is not an accurate rendition of the data in the source cells. OR is it now treating the typed data as a formula and copying the result of that "formula" in the "new" numerical format it assigned? C8 is neither the original source data nor is it presented according to the DATE/TIME format I predefined for it and would expect to see with "paste special" The jury is out on C9 - I no longer know what to expect.
Can you explain what is the core issue that you are concerned about here? That 17:21 changes to a decimal number when pasted to a cell with the generic number format? Otherwise, please give simple steps related to attachment 169940 [details], so anyone can test without thinking.
(In reply to Buovjaga from comment #9) > Can you explain what is the core issue that you are concerned about here? > That 17:21 changes to a decimal number when pasted to a cell with the > generic number format? > No, I think the original issue was that it changed to a decimal when pasted into what was perceived as a text cell in the middle of a column of cells where that column was specifically formatted as text. My original procedure was to "insert" blank rows into the array and assume that the formats would default to the row preceding those being displaced - although I would have expected the same had it taken format from the first displaced row. I was inserting blanks merely to ensure all range IDs expanded appropriately. Also, I have now moved on to; Version: 7.2.5.2 (x64) / LibreOffice Community Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5 CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded and it now produces the text "17:21" as opposed to the decimal. I am (now) also aware that pasting formulaic result "17:21:00" into a "Date" cell doesn't produce a date but a text preceded with an apostrophe "'" and that this is remediated with a regular expression to remove the first character in a CTRL+H operation. Is there a Status "RED HERRING"?🤷♂️
Well, if you are happy with the current situation, we can use worksforme.
(In reply to Buovjaga from comment #11) > Well, if you are happy with the current situation, we can use worksforme. That WORKSFORME 😎