Bug 131536 - FILEOPEN XLSX TEXT formula gives different result than in Excel
Summary: FILEOPEN XLSX TEXT formula gives different result than in Excel
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.0.0
Keywords:
Depends on:
Blocks: Excel-Functions
  Show dependency treegraph
 
Reported: 2020-03-24 14:25 UTC by NISZ LibreOffice Team
Modified: 2022-09-10 17:03 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel (24.28 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-03-24 14:25 UTC, NISZ LibreOffice Team
Details
Screenshot of the original document side by side in Excel and Calc (116.88 KB, image/png)
2020-03-24 14:28 UTC, NISZ LibreOffice Team
Details
The problematic formula structure in the Formula Wizard (43.19 KB, image/png)
2020-03-24 14:28 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2020-03-24 14:25:20 UTC
Created attachment 158936 [details]
Example file from Excel

Attached document contains a TEXT formula that was made in Excel. 
If it is called using the empty string as third parameter, embedded in an IF formula, it gives back a Err:502 result, unlike Excel. 
However if the same formula is called using “0” as third parameter, the error is not present. 
The problem does not appear until the result from TEXT is used in an IF formula, so the example file contains a breakdown of the complex IF formula as well.

Steps to reproduce:
1.	Open attached document.

Actual results:
The formula in D10 gives TRUE in Excel, but Err:502 in Calc.
The same formula with a small change in the TEXT formula is in E10, gives 1 in Calc.

Expected results:
The formula in D10 gives 1 in Calc.

LibreOffice details:
Version: 7.0.0.0.alpha0+ (x64)
Build ID: b07d429f8610c4df93dffa72552f4f759ec0b74f
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win; 
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: CL

Also in:

Verzió: 6.0.0.3
Build az.: 64a0f66915f38c6217de274f0aa8e15618924765
CPU szálak: 4; OS: Windows 6.3; Felületmegjelenítés: GL; 
Területi beállítások: hu-HU (hu_HU); Calc: CL

Version: 5.0.0.5
Build ID: 1b1a90865e348b492231e1c451437d7a15bb262b
Locale: hu-HU (hu_HU)

Version: 4.3.0.4
Build ID: 62ad5818884a2fc2e5780dd45466868d41009ec0

In 4.0 - 4.2 even worse, since the Err:502 appears as result of the simpler formulas, not only IF.
Comment 1 NISZ LibreOffice Team 2020-03-24 14:28:01 UTC
Created attachment 158937 [details]
Screenshot of the original document side by side in Excel and Calc
Comment 2 NISZ LibreOffice Team 2020-03-24 14:28:22 UTC
Created attachment 158938 [details]
The problematic formula structure in the Formula Wizard
Comment 3 Xisco Faulí 2020-03-24 14:52:45 UTC
Reproduced in

Version: 7.0.0.0.alpha0+
Build ID: 9163755e9f64a0b1dd5f2090e0702c19e31c12c9
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded
Comment 4 m_a_riosv 2020-03-24 22:01:08 UTC
At least in calc TEXT() it's not need for the formula.

Not sure empty string it's a valid option for TEXT() function.
https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.pdf
6.20.23 TEXT
Summary: Return the value converted to a text.
Syntax: TEXT( ScalarX ; TextFormatCode )
Returns: TextConstraints:
The FormatCode is a sequence of characters with an implementation-defined meaning.
Semantics: Converts the value X to a Text according to the rules of a number format code passed as FormatCode and returns it.
Comment 5 Eike Rathke 2020-05-05 11:53:26 UTC
An empty format string makes no sense as it would return an empty string for any input value. If that is actually desired the proper format string would be ";;;" (i.e. all empty sub formats).

However, if Excel allows such in TEXT() we could as well. ODFF puts no constraints on TextFormatCode. BUT, what does Excel actually do there? Apparently it does not return an empty string? Does it return a formatted string according to the 'General' format? Something else? Can someone please check?

For example, output for these is ...?
=TEXT(0;"")
=TEXT(12.3456;"")
=TEXT(1E23;"")
=TEXT("lala";"")
Comment 6 Laurent Balland 2020-05-05 12:23:35 UTC
Tests with Excel 2010
(In reply to Eike Rathke from comment #5)
> For example, output for these is ...?
> =TEXT(0;"")
returns empty string
> =TEXT(12.3456;"")
returns empty string
> =TEXT(1E23;"")
returns empty string
> =TEXT("lala";"")
returns "lala"

if argument is numeric (date, time, percent, scientific...) => empty string
if argument is text => string with same text
/!\ if text can be converted as a value, empty string is returned:
=TEXT( "5:3" ; "" ) or =TEXT( "5e3" ; "" )
returns empty string
Comment 7 Eike Rathke 2020-05-05 15:49:02 UTC
Thanks Laurent!

As usual for Excel, I assume the conversion from string to numeric to have the awkward behaviour of being locale dependent, thus indeterminate.

Taking.
Comment 8 Laurent Balland 2020-05-05 16:22:34 UTC
(In reply to Eike Rathke from comment #7)
> As usual for Excel, I assume the conversion from string to numeric to have
> the awkward behaviour of being locale dependent, thus indeterminate.
As usual, you are right ;-)

In French environment, Win7, Excel 2010:
=TEXT( "5 mai" ; "" )
returns empty string, as "5 mai" is detected as a date, while
=TEXT( "5 May" ; "" )
returns "5 May", as English date is not detected.

Good luck
Comment 9 Commit Notification 2020-05-05 19:34:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b7a53077fd592c514d2f255cc19e4f0deb483dca

Resolves: tdf#131536 TEXT() support empty format string mimicking Excel

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Xisco Faulí 2020-05-06 14:25:51 UTC
Verified in

Version: 7.0.0.0.alpha0+
Build ID: 1eb6210d678bbb0c875f0f72b...
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

@Eike, thanks for fixing this issue!!
Comment 11 Commit Notification 2020-05-06 21:50:16 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/be29bd29864e450b0a99898d560ba71562cf06da

tdf#131536: sc: Add unittest

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.