Description: After correcting the format of a cell that until then contains a date in unformatted text writing to correct date type the formular of an aligning cell does not update its value from "#WERT!" to value expected by formula in this cell (year of the cell). Steps to Reproduce: 1.CALC: Create a rather large table to track monthly financial expenses. Table contains 6 columns. Second column is "Jahr" which uses formula JAHR(#cell) to extract calender year of aligning column 3 that is labelled "Datum" 2. In cell C4240 place a date string in unformatted format but indicating a valid date. Cell format currently is NOT "date" type. Date string is "Mi., 01. Nov 2023" which is a date string taken from another CALC sheet copied, just the format is not copied, only text, so in principle this is an exact date formatted string and should be recognized as a date formatted string. 3. In cell B4240 place formula "=JAHR(C4240)" to extract calender year from column C date formatted cells. 4. Change cell format of C4240 to date (right mouseclick-> "Zellen formatieren" -> Datum, use entry that fully matches the date string, -> "OK" Actual Results: 3. Cell B4240 correctly shows "#WERT!" as cell C4240 is not formatted as a date format. (OK) 4. BUG: Cell B4240 fails and still shows "#WERT!" (NOK) Expected Results: 4. Cell B4240 should now show value 2023 as this is the year-part of the date string in C4240. Workaround: in cell C4240 type exactly the same date string again. Cell B4240 will then magically switch to 2023 as expected before. Apparently the option to change cell format is totally useless as somehow the overlying processes are somehow not notified that the format of a cell has changed and a new format was applied to it. This only happens after tediously manually entering the whole cell again by typing the exact string over again. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.8.2 (X86_64) / LibreOffice Community Build ID: 50(Build:2) CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.utf8); UI: de-DE Debian package version: 4:7.5.8-1 Calc: threaded
Please attach a sample file?
Created attachment 190655 [details] Example Sheet to reproduce error. I have attached an example sheet. How to reproduce error: (1) Open error.ods in CALC and browse to TAB "Einkaufszettel", (2) starting in line 4, drag mouse across area A:4 to C:10 and press CRTL+C to copy cell area to clipboard, (3) browse to TAB "Laufende Ausgaben", (4) do right mouse click in B:14 and select "Inhalte einfügen" -> "Unformatierter Text" -> OK (this is needed as selected and copied data contains formulae and we want only values here), (5) NOK: after data was pasted starting B:14, column "Jahr" shows "#WERT!" which is wrong as cell format in B:14 to B:20 was identified as "Datum" already. - NOK: Changing format of cells B:14 to B:20 to "Date" does not fix issue. - Workaround: Only manually entering same date string over again fixes issue.
The issue is that your format is not acceptable as input for a date, then it is pasted as text in the cell. At paste, select the column and select type of date DD/MM/YYYY, so it is pasted as date. Or look for format that is accepted as date input.
This cannot be the cause. After finalizing copy (i.e. after copying the area of aligning cells as non-formatted text) the date cells are actually identified as date, and they have the same text string as before, when they were also date type cells. If you were right, the resulting date cells should have no format applied, but they are identified as date format cells, but the format is not recognized as date and the date cannot be extracted,but they have exactly the same text value of date as before, when the exact same date type string was identified as date cell on the other TAB of sheet.
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to crptdngl71 from comment #0) > 2. so in principle this is an exact > date formatted string and should be recognized as a date formatted string. That is not accurate. The date acceptance pattern options are relevant. Additionally, you could have the cells formatted adequately before pasting. But even then, if you paste as unformatted text, there is no reason for the data to be considered as date. I agree with comment 3, NAB.
Ah I think, I've found it now :-) Instead of step pasting unformatted strings use "Inhalte einfügen"-> Inhalte einfügen" and select "Zahlen", "Text" and "Datum und Uhrzeit" as parts to be pasted. This will then paste the raw date string (without user defined addons, such as ","), text strings and numeric values of TAB "Einkaufszettel" column D. I am sorry about my wrong understanding about how this should work. This ticket can be closed.