Please attach a sample file, to see what it's the cell's content.

Look to me that those cells have text dates, not spreadsheet dates.

Created attachment 179794 [details]
Sample file for the described error.

Cells have a date in date format.

Sorry, which is the with the formula not working?

When I insert another or a new statement in the sheet "Výpis CZK" or "Výpis EUR", then where the month is displayed with one number in the date (eg 9.2021), the formula does not take it, because the formula wants 09.2021.

Created attachment 179807 [details]
Statement saved from platform for testing

Comment on attachment 179807 [details]
Statement saved from platform for testing

I insert the content of the document by simply copying CTRL-C, CTRL-V.

It's unclear to me where is the problem. Please can you prepare some simple example or explain better?

(In reply to raal from comment #9)
> It's unclear to me where is the problem. Please can you prepare some simple
> example or explain better?

Set to NEEDINFO.
Change back to UNCONFIRMED after you have provided the information.

Jastli tu mluví někdo česky, tak bych se to mohl pokusit vysvětlit česky, ale myslím, že to Google přeložil dobře. Nevím co víc k tomu napsat. Neumím skoro vůbec anglicky. Už je to dlouho, kdy jsem to psal. Zatím používám pořád starší verzi.

Prostě chci hledat pomocí funkce ve sloupci s datumy, kde jsou datumy v tom dokumentu zaznamenané jako DD.MM.YYYY ale Calc to zobrazí jako DD.M.YYYY a nevím jak to změnit, aby se v tom dokumentu zobrazoval původní formát a ne změněný. Ta funkce pak nemůže rozlišit, když je v datumu např 2.11.2021 nebo 2.1.2021, když bych změnil to filtrování z "01.2021" na "1.2021"

Sorry, sent prematurely.

(In reply to ady from comment #13)
After spending too-much time with attachment 179794 [details] and
re-re-re-reading the posts here, (and also wasting additional time because
Calc lacks "Trace dependents" features), I would say that there is no bug
here (but, I could be wrong).

I tested with LO 7.0.0.3 and with LO 7.4.5. In both, the first column A:A in
the first sheet (named "Výpis CZK") is formatted as "D.M.YYYY". In either of
these 2 versions the cells in this column are recognized as "DD.MM.YYYY" or
anything like that, other than "D.M.YYYY".

Moreover, you are using the function CONCAT (for example in sheet "Výpočet 2" column C (e.g in C3) as criterion to compare to the aforementioned "date"
cells, which then triggers the SUM in SUMIFS.

You probably want some other function such as YEAR(), instead of a text-related function.

Anyway, I would suggest checking:

 https://ask.libreoffice.org/c/czech-and-slovak/13

Sorry, je to už  dávno co jsem to psal, tak jsem si to špatně zapamatoval. Ano, soubor obsahuje D.M.YYYY, ale když kliknu na ten datum tak se ve vstupní řádce ukáže DD.MM.YYYY a to se asi v té nové verzi neukazovalo a ukazovalo se tam D.M.YYYY (už si to nepamatuju přesně).

Prostě ve staré verzi mě to fungovalo a po přeinstalování na novou verzi ten vzorec najedou místo DD.MM.YYYY používal D.M.YYYY a neměnil jsem žádné nastavení, tak jsem to považoval za bug. Ani jsem nikde v nastavení nenašel jak změnit formát datumu pro nově otvírané dokumenty pokud by v nové verzi se toto nějak změnilo.

Možná to někdy zkusím zase v nejnovější verzi. Ale je to zajímavé, že pouhé přeinstalování verze bez jakékoliv změny v nastavení změní fungování vzorců.

(In reply to JPT from comment #15)
> Sorry, je to už  dávno co jsem to psal, tak jsem si to špatně zapamatoval.
> Ano, soubor obsahuje D.M.YYYY, ale když kliknu na ten datum tak se ve
> vstupní řádce ukáže DD.MM.YYYY a to se asi v té nové verzi neukazovalo a
> ukazovalo se tam D.M.YYYY (už si to nepamatuju přesně).
> 

Zkoušel jsem to ve staré verzi a tam to je stejné jako v současné verzi.

> Prostě ve staré verzi mě to fungovalo a po přeinstalování na novou verzi ten
> vzorec najedou místo DD.MM.YYYY používal D.M.YYYY a neměnil jsem žádné
> nastavení, tak jsem to považoval za bug. Ani jsem nikde v nastavení nenašel
> jak změnit formát datumu pro nově otvírané dokumenty pokud by v nové verzi
> se toto nějak změnilo.

Regrese jdou celkem jednoduše identifikovat, ale pořád mi není jasné kde je chyba. Pokračujme prosím v diskuzi v poradně https://ask.libreoffice.org/c/czech-and-slovak/13 , založte zde vlákno s odkazem na tuto chybu. Ideálně také připravte jednoduchý příklad, kde bude vidět chyba.


> 
> Možná to někdy zkusím zase v nejnovější verzi. Ale je to zajímavé, že pouhé
> přeinstalování verze bez jakékoliv změny v nastavení změní fungování vzorců.

Může to být chyba nebo oprava/záměr. Jakmile zjistíme který commit to způsobil, bude jasno.

Changed in commit https://gerrit.libreoffice.org/c/core/+/112343   

Adding CC to: Balazs Varga

Created attachment 185596 [details]
test files

Steps to reproduce:
 open file “obraty CZK.xlsx”
 copy columns A:G
 open file “Souhrný výpočet z výpisu transakcí - Update 2022.04.24 - Template.ods”
 go to sheet "Výpis CZK"
 paste data CTRL+V
 go to sheet "Výpis CZK"
 See columns DB:BO

The penultimate line is wrong. Is to be there:
go to sheet "Výpočet 2"

I admit I don't know (or rather, understand) what's the problem or how to trigger it, so IDK why this is set to "new". I'm not saying it shouldn't; I'm just saying I don't understand it.

To put it bluntly (if I may, with no bad intention really), as it is at this moment, there is no way whatever the heck is happening here will be fixed.

* The steps to reproduce are not clear (in no comment until now).
* There is no clear indication on which specific cell (range) we should look at in order to identify what is that seems to be wrong (and I tried, again, with different LO versions).
* The cell formats are inconsistent. Some are D.M.YYYY, some are standard numeric values, and some are text (such as "BD:BO" mentioned before).
* There is a mix of formulas, using text-related functions to compare date-formatted cells against numbers or text-formatted cells.
* The sample files are not trimmed in order to show the main problem; a lot of unnecessary "noise", with a lot of unrelated cells, unrelated sheets, unrelated formulas, and non-English characters that are unnecessary in this context – is it so hard to use "sheet1" for a sample file?
* The posts here are not in English. If I can use a web-based translator, I think others could too.
* Is there any post at ask.libreoffice.org in whichever language, related to this issue?

Please don't take these comments as complaints, but rather as suggestions, in order to get whatever is wrong to be fixed at some point.

I guess I am saying that some simplification and clarity would be welcome, in order for some developer to be able to do something, and so this can be replicated by someone else, whether now or if/when a patch is proposed in the future.

Sorry, but I felt that I described it well and when someone wrote here that they still don't understand it, I didn't have the strength to write here in English anymore, because it seems to me that the translator won't translate it very well and I have to from the word to check if it can be so in English. And if I don't speak English well, it takes me a very long time. For now, I'll just try to copy it, as the translator will translate it.

I tried to send exactly the file where the error is, but I will try to cut it so that there are no unnecessary things.

As recommended by raal, I created a post in Czech here https://ask.libreoffice.org/t/spatne-pouziti-formatu-datumu-ve-vzorcich/88491

I originally wanted to start a thread in Czech, but in Czech there is only a forum or an ask, and I found bugs in English, so I wrote it here because I thought that someone here would look at it and try to fix it, and elsewhere they didn't help.

Created attachment 185609 [details]
Test files in English

Steps to reproduce:
 open file “Statement.xlsx”
 copy columns A:G CTRL+C
 open file “Calculation.ods”
 go to sheet "Statement"
 paste data CTRL+V to cell A1
 go to sheet "Calculation"
 See columns D:O

In version 7.1.8.1 I see all the filtered amounts per month (image "Version 7.1.8.1.jpg")
In version 7.4.5.1 I don't see all the filtered amounts per month (picture "Version 7.4.5.1.jpg")

(In reply to JPT from comment #22)
> Created attachment 185609 [details]
> Test files in English

Once again, I tried with version 7.4.5 and with 7.0.0.3. Both show the same, all zeroes. So, for me, no repro.

I am just going with a hunch here: Is there any chance that the change in how apostrophes are parsed could be somehow related? This is, considering that the range D1:O1 (mentioned above already twice) is text, not date nor number.

Also, is there a chance this is related to the specific version of OS (Windows 6.1 Service Pack 1 Build 7601)?

What you have in Calculation.D1 it is not a date but text. So can't be used to search for a date.
Introduce in it 31/01/2022, and if you like format as MM.YYYY.
But you need to change the formula, if you want to use SUMIFS
D2: =SUMIFS($Statement.$E:$E;$Statement.$C:$C;"*"&$'Help data'.$A5&"*";$Statement.$A:$A;">="&DATE(YEAR(D$1);MONTH(D$1);1);$Statement.$A:$A;"<="&D$1)
or with SUMPRODUCT, but using entire columns degrades too much the performance because doesn't shortcut at the end of data, it calculates the whole rows.
d2: =SUMPRODUCT($Statement.$E2:$E9999;IFERROR(SEARCH("*"&$'Help data'.$A$8&"*";$Statement.$C2:$C9999;1);0);MONTH($Statement.$A2:$A9999)=MONTH(D$1))

(In reply to m.a.riosv from comment #24)
> What you have in Calculation.D1 it is not a date but text. So can't be used
> to search for a date.
> Introduce in it 31/01/2022, and if you like format as MM.YYYY.

@m.a.riosv,

While I agree that the formulas are not ideal, there are others that are seeing some different behavior depending on the version of LO. This is why raal and Buovjaga agreed to set it to NEW.

I wasn't able to reproduce whatever non-zero values others are seeing with an older version of LO, but others apparently do.

Whichever criticism the specific formulas might deserve, it doesn't explain the different behavior between versions.

I am not going to revert the setting of NAB (m.a.riosv) back to NEW, but maybe either raal or Buovjaga should.

Again, the fact that the formulas are not ideal does not explain the change in behavior, so such change might or might be intentional. Thus, this might or might not be a bug.

(In reply to ady from comment #25)
> Again, the fact that the formulas are not ideal does not explain the change
> in behavior, so such change might or might be intentional. Thus, this might
> or might not be a bug.


Sorry, of course I meant:
* this might or might _not_ be a bug.

Created attachment 185642 [details]
Screenshot Version 7.1.8.1 - Windows 10

(In reply to ady from comment #23)
> (In reply to JPT from comment #22)
> > Created attachment 185609 [details]
> > Test files in English
> 
> Once again, I tried with version 7.4.5 and with 7.0.0.3. Both show the same,
> all zeroes. So, for me, no repro.
> 
> I am just going with a hunch here: Is there any chance that the change in
> how apostrophes are parsed could be somehow related? This is, considering
> that the range D1:O1 (mentioned above already twice) is text, not date nor
> number.
> 
> Also, is there a chance this is related to the specific version of OS
> (Windows 6.1 Service Pack 1 Build 7601)?

I tried the 7.1.8.1 version on Windows 10 and it still shows fine for me. Either the error appears only with the Czech translation of LibreOffice or it appears with every version of Windows in the Czech language.

(In reply to m.a.riosv from comment #24)
> What you have in Calculation.D1 it is not a date but text. So can't be used
> to search for a date.
> Introduce in it 31/01/2022, and if you like format as MM.YYYY.
> But you need to change the formula, if you want to use SUMIFS
> D2: =SUMIFS($Statement.$E:$E;$Statement.$C:$C;"*"&$'Help
> data'.$A5&"*";$Statement.$A:$A;">="&DATE(YEAR(D$1);MONTH(D$1);1);$Statement.
> $A:$A;"<="&D$1)
> or with SUMPRODUCT, but using entire columns degrades too much the
> performance because doesn't shortcut at the end of data, it calculates the
> whole rows.
> d2: =SUMPRODUCT($Statement.$E2:$E9999;IFERROR(SEARCH("*"&$'Help
> data'.$A$8&"*";$Statement.$C2:$C9999;1);0);MONTH($Statement.$A2:
> $A9999)=MONTH(D$1))

Thanks for listing the other options. Sometimes I might try to apply them to new tables. It will be easier for me now (if I want to use new versions of LibreOffice) to change the search criteria from "MM.YYYY" to ".M.YYYY" so it works in the new version. Well, at least me. However, it does not work in the old version.

(In reply to JPT from comment #28)
>  However, it does not work in the old version.

Between this and comment 27, and the others that reported reproducing the problem, I am reopening this. The fact that one (or even two) users were not able to reproduce it does not mean it should be closed. Perhaps it is NAB, but perhaps it is.

Hopefully someone at some point will be able to understand and explain clearly enough so it can be solved.

Created attachment 185653 [details]
Calculations file with DD.MM.YYYY formatted dates.

Reported file Calculation.xlsx, works fine if date cell in Statement.A:A column, has the format as DD.MM.YYYY not as D.M.YYYY
If data are searched in the date format, no like date values,
looking for 01.2022 in a date like 4.1.2022 doesn't work, but it does on 04.01.2022.

Maybe the behavior change with the resolution of tdf#144740, that was applied from 7.2.2

But looks to me that now is consistent.

Works in this way at least from
Version: 7.2.7.2 (x64) / LibreOffice Community
Build ID: 8d71d29d553c0f7dcbfa38fbfda25ee34cce99a2
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: es-ES Calc: CL

(In reply to m.a.riosv from comment #30)

Yes, I know that if I change the format of the input file then it will work, but I created those tables for different platforms so that I can edit only once at the beginning the search criteria for each one and not have to edit the input file every time if there is no date given in the appropriate format.

It worked before, now it doesn't. If it's not a bug, I'll use ".M.YYYY" instead of "MM.YYYY" for searches in the new versions, it'll be easier than fixing the formulas or editing the input file every time.

(In reply to JPT from comment #31)
> (In reply to m.a.riosv from comment #30)
> 
> ......
> It worked before, now it doesn't. If it's not a bug, I'll use ".M.YYYY"
> instead of "MM.YYYY" for searches in the new versions, it'll be easier than
> fixing the formulas or editing the input file every time.
Please, with what date format in Statement.A:A and what string in calculation.C1 works for you?.

(In reply to m.a.riosv from comment #32)
> 
> Please, with what date format in Statement.A:A and what string in
> calculation.C1 works for you?.

On the original platform statement is "D.M.YYYY".

In the calculation in cell C1 (only "YYYY") works in both versions.

In the calculation in cells D1:O1, "MM.YYYY" works in version 7.1.8.1 and below.
In version 7.4.5.1 (probably from version 7.2.6 and above), ".M.YYYY" works on that same statement.

(In reply to JPT from comment #33)
> In version 7.4.5.1 (probably from version 7.2.6 and above), ".M.YYYY" works
> on that same statement.

But that is not the current content of "Calculation.D1:O1" (after pasting the needed values from the other worksheet), is it?

When I review "Calculation.ods" and review the cell format (ctrl+1):

Calculation.D1: General
Calculation.E1: 0
Calculation.F1: #,##0.00;[RED]-#,##0.00

and the content of these cells start with an apostrophe (').

Under those conditions, and with the values of Statements.xls copied to the Statement worksheet in Calculation.ods, then the results in the Calculation worksheet are all zeroes for me (LO 7.4.5).

Now, if I go to D1 and I type in "01.2022" (without quotation marks) over the previous "'01.2022" (which includes an apostrophe), then I do get non-zero values in the same column.

Is it possible that the recent change in how the apostrophe is working in LO has anything to do with this?

The same goes to D2:O2. Of course, the way these cells are displayed is different, and they depend on the aforementioned formats.

My point is that whatever you see, depend on several things. I don't care that the formulas are not ideal, because there might be a bug anyway, and there seem to be other factors too.

Since raal was able replicate, and the screenshots also show non-zero values for older versions (whihc I don't see), there is not only a communication problem in this report, but perhaps there is some locale factor too(?).

raal? Can you please clarify this? What are the steps you followed? Is there a bug that you can replicate, or not?

(In reply to ady from comment #34)
> (In reply to JPT from comment #33)
> > In version 7.4.5.1 (probably from version 7.2.6 and above), ".M.YYYY" works
> > on that same statement.
> 
> But that is not the current content of "Calculation.D1:O1" (after pasting
> the needed values from the other worksheet), is it?

No. I have to change the original "MM.YYYY" to ".M.YYYY" to make it work like in 7.1.8.1

> 
> When I review "Calculation.ods" and review the cell format (ctrl+1):
> 
> Calculation.D1: General
> Calculation.E1: 0
> Calculation.F1: #,##0.00;[RED]-#,##0.00

I didn't even know that was the case. I didn't care what the format was when everything worked the way I wanted it to.

> 
> and the content of these cells start with an apostrophe (').

I don't have the apostrophe there either in version 7.1.8.1 or 7.4.5.1. But to tell the truth, sometimes it happens to me that I have apostrophes in foreign documents, I think most often in the dates. I already have a macro saved for deleting the apostrophe.

> 
> Under those conditions, and with the values of Statements.xls copied to the
> Statement worksheet in Calculation.ods, then the results in the Calculation
> worksheet are all zeroes for me (LO 7.4.5).
> 
> Now, if I go to D1 and I type in "01.2022" (without quotation marks) over
> the previous "'01.2022" (which includes an apostrophe), then I do get
> non-zero values in the same column.

I have zeros there (in versions higher than 7.1.8.1) but I have nothing to delete (apostrophe). Neither overwriting nor deleting and rewriting works. Still zeros.

> but perhaps there is some locale factor too(?).


I'll add that at first I thought I had set something in the settings and it changed from a certain version after reinstalling, but I installed a fresh installation of version 7.4.5.1 in a virtual PC, so it probably won't be that. It will probably be a locale setting. I can try to install English in LibreOffice and see what it does there.

(In reply to ady from comment #34)

> and the content of these cells start with an apostrophe (').


> Under those conditions, and with the values of Statements.xls copied to the
> Statement worksheet in Calculation.ods, then the results in the Calculation
> worksheet are all zeroes for me (LO 7.4.5).
> 
> Now, if I go to D1 and I type in "01.2022" (without quotation marks) over
> the previous "'01.2022" (which includes an apostrophe), then I do get
> non-zero values in the same column.


> but perhaps there is some locale factor too(?).

(In reply to JPT from comment #35)
> I can try to install English in LibreOffice and see what it does there.

If I change the local setting to English, the apostrophe suddenly appears in the D1:O1 cells, but when I change to Czech it disappears again. 


When I switch Locale settings to English, the "Date acceptance patterns" is set to "M/D/Y;M/D" and this changes the display of dates in the statement, so then the "calculation" sheet cannot calculate anything. If I set the English pattern to "D.M.Y;D.M.;D. M.;D. M. Y;D. M.;D. M. Y" , it stays in the settings, but the date display does not change. "D.M.Y;D.M.;D. M.;D. M. Y;D. M.;D. M. Y" will only stay there until I switch to another language and put it back. Then it's back to the original "M/D/Y;M/D".


If I overwrite the values with an apostrophe to "01.2022", it switches to "1.2022", it doesn't stay there "01.2022", but that wouldn't help me anyway, because the dates in the listing have changed to "D/M/YYYY" because of the Locale setting, which can't be changed.


So I can't replicate the fact that when I overwrite the cell with the apostrophe to the same value, it suddenly starts counting correctly (non-zero values appear).

> If I change the local setting to English, the apostrophe suddenly appears in
> the D1:O1 cells, but when I change to Czech it disappears again. 

Finally we can see one important factor that makes the difference in our reported experiences.

The real question to solve all this is _what_ exactly made older version in Czech to behave as they did whereas newer versions behave differently, making this file fail when they worked before. The change in behavior might be intentional, or might be a bug, but we don't know yet what provoked this change.

We also have a locale factor here, when LO in English behaves one way, whereas in Czech it behaves differently. The results of formulas shouldn't be different just because of LO language.

These two factors are the reason for me to think that this should not be set as NAB at the moment. Something happened; and the fact that the formulas are not ideal are not relevant in this case, IMO. And @raal thought this too, according to his comments here and in ask.libreoffice.org.


> When I switch Locale settings to English, the "Date acceptance patterns" is
> set to "M/D/Y;M/D" and this changes the display of dates in the statement,
> so then the "calculation" sheet cannot calculate anything. If I set the
> English pattern to "D.M.Y;D.M.;D. M.;D. M. Y;D. M.;D. M. Y" , it stays in
> the settings, but the date display does not change. "D.M.Y;D.M.;D. M.;D. M.
> Y;D. M.;D. M. Y" will only stay there until I switch to another language and
> put it back. Then it's back to the original "M/D/Y;M/D".

Please don't mix these features. One thing is what Calc will automatically interpret as a date when introducing a value in a cell. Another thing is the cell's format, which can be changed in several ways. If I have a date in a cell that is displayed as "01.2023" (without quotation marks) and it is displayed as "MM.YYYY", I could change the cell format to something else, such as "MMM YYYY" for example.

As I tried to express before, we need to focus on the changed behavior(s), whether they are about the locale in cells, UI language, OS's settings, or some change in some version of LO. Things that seem to be not relevant in this specific bug report, such as how the formulas are built, or how to display a date (e.g. with or without leading zero) should not be a factor to set this report to NEW.


> If I overwrite the values with an apostrophe to "01.2022", it switches to
> "1.2022", it doesn't stay there "01.2022", but that wouldn't help me anyway,
> because the dates in the listing have changed to "D/M/YYYY" because of the
> Locale setting, which can't be changed.
> 
> 
> So I can't replicate the fact that when I overwrite the cell with the
> apostrophe to the same value, it suddenly starts counting correctly
> (non-zero values appear).

I can also simply delete the apostrophe, leaving the "1.2022", and the resulting formulas change to non-zero.

It is certainly intriguing why you are not able to see the apostrophe when the UI is set to Czech. This is independent of the formulas. Another clue that this must be investigated, instead of setting to NAB.

I can only hope that either raal comes with some explanation, and/or Miguel changes his mind, and/or someone else can replicate this with some kind of relevant logical explanation as to why this changed for Czech UI in version 7.2, whereas in English the behavior is a fail with older and with newer versions.

Changed to unconfirmed.

Who think it is a bug, change it to 'new'

After several tests using attachment 185609 [details] using these steps (based on comment 22):

0. *Initial* step (key importance, in order to be able to reproduce results): for each test, set first Tools > Options > Language Settings > Languages > Locale Setting to the corresponding language (one round with English, second round with Czech), before the rest of the test. For one round of tests I used English Locale; for the second round of (the same) tests I used Czech Local. Changing the UI language did not influence my observations, but the Locale setting did.
1. Open Calculation.ods and Statement.xlsx.
2. Copy the whole range of values from Statement.xls.
3. Paste Values onto Calculation.ods > Statement (worksheet)
4. Change to see Calculation.ods > Calculation (worksheet)
5. Review from D1 to O1 and the resulting values in D2:O10.

I arrived to the following observations:
A_ The number format of D1 to O1 are as I already described before in comment 34 (D1 – General/Standard; E1 – 0, and then the rest with "# ##0,00;[RED]-# ##0,00" up to O10). Warning: this format can only be seen when opening Calculation.ods in recent 7.6.alpha+. When opening Calculation.ods with older versions, the number format is slightly different: "#,##0.00;[RED]-#,##0.00". Whether this could be part of the problem (considering the less-than-ideal formulas), I do not know. The different behavior has been seen by the OP (at least) since LO 7.2, but IDK whether some other changes in number format codes were introduced back then already.

B_ The UI language itself does not modify the appearance (or not) of the apostrophe in D1:O1 in the formula bar.
C_ The setting that indeed influences the appearance of the apostrophe in the formula bar is Tools > Options > Language Settings > Languages > Locale Setting.
C.1_ Locale setting in Czech, the apostrophe is _not_ displayed in the formula bar.
C.2_ Locale setting in English, the apostrophe shows up in the formula bar.
D_ The date pattern acceptance for Czech is "D.M.Y;D.M.;D. M.;D. M. Y;D. M.;D. M. Y". Is there a chance that the space characters here are not simple spaces but others, such as thin? Could this be part of the changed behavior? Was there any changes in the Czech Locale since LO 7.1?
E.1_ While using Czech Locale, I am able to see the resulting non-zero values in older versions of LO. The _initial_ change in Locale is key (i.e. step "0" above).
E.2_ While using English Locale, I am _not_ able to see the resulting non-zero values (under columns D to L in Calculation.ods>Calculation worksheet), no matter with which version I tested this setup, unless I delete the apostrophe.
E.3_ While using English Locale, I can delete the apostrophe in D1:O1 and then some non-zero values are displayed under the respective cells/columns.
F_ The language in the status bar shows Czech (for alphabet, spelling, etc.). While using Czech as Locale, I can change the individual cells to show English ([CTRL]+[1]), and then the apostrophe is displayed in the formula bar in D1 to O1.

Let me be clear about the observations:
* I can make the apostrophe show up or disappear from the formula bar by changing the Locale between Czech and English(USA) "on-the-fly".
* In order to obtain non-zero results, I have to set the Locale to Czech _before_ copy+pasting the values from Statement.xlsx to Calculation.ods>Statement, and this can only be achieved in older versions of LO. In newer versions, the results are always zero, until I remove the apostrophe, which can only be done in English Locale.

Conclusion:
* Changed behavior and calculation results according to Locale
* Changed behavior and calculation results according to version.
* Number format code recognized differently according to newer support. This _seems_ unrelated, but cannot be discarded (yet).

IDK which combination is "correct" and whether the other combinations imply that there is a bug somewhere (since 7.2.6 or so?).

@raal, you seem to think that there is a bug, since you set this to "regression", "bibisected" and "bisected", and even pointed to a specific commit. Either you or someone else confirming this would be helpful.

(In reply to raal from comment #18)
> Created attachment 185596 [details]
> test files
> 
> Steps to reproduce:
>  open file “obraty CZK.xlsx”
>  copy columns A:G
>  open file “Souhrný výpočet z výpisu transakcí - Update 2022.04.24 -
> Template.ods”
>  go to sheet "Výpis CZK"
>  paste data CTRL+V
>  go to sheet "Výpis CZK"
>  See columns DB:BO

Hi raal,
Could you explain what is the expected/current behaviour ?

(In reply to Xisco Faulí from comment #40)
> (In reply to raal from comment #18)
> > Created attachment 185596 [details]
> > test files
> > 
> > Steps to reproduce:
> >  open file “obraty CZK.xlsx”
> >  copy columns A:G
> >  open file “Souhrný výpočet z výpisu transakcí - Update 2022.04.24 -
> > Template.ods”
> >  go to sheet "Výpis CZK"
> >  paste data CTRL+V
> >  go to sheet "Výpis CZK"
> >  See columns DB:BO
> 
> Hi raal,
> Could you explain what is the expected/current behaviour ?

Hi Xisco, please open attached zip file. See two files
Výpočet 2 - Verze 7.1.8.1.jpg - before the commit
Výpočet 2 - Verze 7.4.5.1.jpg - after the commit
Numbers in columns BD:BL are missing