[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893 Thorsten Behrens (allotropia) changed: What|Removed |Added CC||t...@libreoffice.org --- Comment #10 from Thorsten Behrens (allotropia) --- (In reply to ady from comment #8) > Considering that the problem affects XLOOKUP() (at least), are you planning > on assigning this to yourself? > This is a general issue, and only coincidentally related to the new XLOOKUP implementation (it might be more obvious there, though). It's unlikely we get to this in the near future. -- You are receiving this mail because: You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893 --- Comment #9 from m_a_riosv --- Maybe some relation with tdf#144740 Criteria query (COUNTIF(), MATCH() et al) for numeric values broken considering number formats tdf#144253 Filtering does not work with formatted calculated values -- You are receiving this mail because: You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893 --- Comment #8 from ady --- @Balázs Considering that the problem affects XLOOKUP() (at least), are you planning on assigning this to yourself? If I may suggest, let's see what happens when solving this for XLOOKUP(), and then we can re-check the others (whether the patch for XLOOKUO() solves any other functions). -- You are receiving this mail because: You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893 Gabor Kelemen (allotropia) changed: What|Removed |Added CC||kelem...@ubuntu.com --- Comment #4 from Gabor Kelemen (allotropia) --- Created attachment 194238 --> https://bugs.documentfoundation.org/attachment.cgi?id=194238=edit The original in ods and its xlsx version in Excel 2021 Indeed there is a difference in results, with current nightly: Version: 24.8.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: ae798781ef4df7a1fdef13af0bc459bf4f6e7b4c CPU threads: 6; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded -- You are receiving this mail because: You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893 --- Comment #3 from ady --- @Balázs Polite ping. XLOOKUP: Internal cell value vs display value (comment 2). I cannot test with Excel by myself ATM. @All, This report is still set as UNCONFIRMED. Is this a bug, or not? We should rather know it (and be solved if it is) before LO 24.8 beta arrives. I guess the same question – internal cell value vs display value – would be relevant for XMATCH()? -- You are receiving this mail because: You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893 Roman Kuznetsov <79045_79...@mail.ru> changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=12 ||7293 Blocks||108827 Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=108827 [Bug 108827] [META] Calc functions bugs and enhancements -- You are receiving this mail because: You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893
--- Comment #2 from ady ---
(In reply to Regina Henschel from comment #0)
> Problem:
> (1) If the Lookup_value is a text that looks like a date, it is interpreted
> as number.
> (2) If the Lookup_array contains dates, neither a lookup_value in serial
> date form nor as result of the DATE function matches the dates.
While I don't have access to Excel 2021 to test its results ATM, I think that
the description in comment 0 given to the results from Calc is slightly
inaccurate.
I think the problem (assuming that Excel 2021 provides the results as in
attachment 193922 cell range B7:B10, which I cannot review by myself ATM) is
that Calc is comparing/matching according to the ** display format **, rather
than according to cell value.
STR:
1. Open attachment 193922.
2. Note the resulting value of cell C7: 33.12.
3. On cell A2, change (display) format from original -MM-DD to YY-MM-DD.
4. Recalculate Hard.
5. Note that the result on cell C7 changed from 33.12 to 86.92.
Explanation (my guess):
In cell C7:
=XLOOKUP("2024-01-15";A2:A4;B2:B4;"no invoice";0;2)
is looking for "2024-01-15" and originally found the first match in cell A2.
Once cell A2 changed its (display) format, XLOOKUP no longer finds "2024-01-15"
in cell A2, despite cell A2 containing the same internal value (serial
date/time "45306"). The new first match is located in cell A3, still displaying
the same value in the same display format that matches "2024-01-15".
Conclusion (my guess):
Calc's XLOOKUP is matching the values according to display format, not by
internal value.
Assuming the values in cell range B7:B10 are to be taken as correct – I just
don't have any way to confirm it ATM by myself – then Excel 2021 would seem to
perform the comparison/match according to internal value, not by "display
format". Please don't just trust this and test by yourself, because I might
very well be wrong and I cannot test this by myself ATM.
(In reply to m_a_riosv from comment #1)
> Created attachment 193924 [details]
> Sample file with XLOOKUP and MATCH
> $3,00 $2,00 =MATCH("2024-01-15";A2:A4;0)
> 1 #N/A=MATCH(DATE(2024;1;15);A2:A4;0)
> $3,00 $2,00 =MATCH(T("2024-01-15");A2:A4;0)
> 1 #N/A=MATCH(45306;A2:A4;0)
I get a different result than that. For instance:
=MATCH("2024-01-15";A2:A4;0)
results in "1" (not "2") in Calc (after Recalculate Hard).
Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: bc7501d30ab9a3a5edec66d2f0022177f85d6127
CPU threads: 4; OS: Windows 10 (10.0 build 19045); UI render: Skia/Raster; VCL:
win
Locale: en-US (es_AR); UI: en-US
Calc: CL threaded
At any rate, we should be careful when comparing XLOOKUP/XMATCH with the older
VLOOKUP/MATCH functions, considering the explicit argument for "binary search"
available in the newer "X" functions. We also don't always know whether the
"sorting" used in Calc's functions (for the binary search) is _exactly_ the
same sorting order used in Excel.
--
You are receiving this mail because:
You are the assignee for the bug.
[Bug 160893] XLOOKUP problem with date as text
https://bugs.documentfoundation.org/show_bug.cgi?id=160893
m_a_riosv changed:
What|Removed |Added
CC||miguelangelrv@libreoffice.o
||rg
--- Comment #1 from m_a_riosv ---
Created attachment 193924
--> https://bugs.documentfoundation.org/attachment.cgi?id=193924=edit
Sample file with XLOOKUP and MATCH
It seems to have the same differences as MATCH().
Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: f4ef5435df5560e6b6b061ce4053c71e2819bf51
CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win
Locale: es-ES (es_ES); UI: en-US
Calc: CL threaded
Excel
$71,29 $86,92 =XLOOKUP("2024-01-15";A2:A4;B2:B4;"no invoice";0;2)
33,12 no invoice =XLOOKUP(DATE(2024;1;15);A2:A4;B2:B4;"no invoice";0;2)
$71,29 $86,92 =XLOOKUP(T("2024-01-15");A2:A4;B2:B4;"no invoice";0;2)
33,12 no invoice =XLOOKUP(45306;A2:A4;B2:B4;"no invoice";0;2)
$3,00 $2,00 =MATCH("2024-01-15";A2:A4;0)
1 #N/A=MATCH(DATE(2024;1;15);A2:A4;0)
$3,00 $2,00 =MATCH(T("2024-01-15");A2:A4;0)
1 #N/A=MATCH(45306;A2:A4;0)
--
You are receiving this mail because:
You are the assignee for the bug.
