Issue 108385 - TEXT should accept textual argument
Summary: TEXT should accept textual argument
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOO320m9
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: regression
Depends on:
Blocks: 111112
  Show dependency tree
Reported: 2010-01-16 23:22 UTC by armando_it
Modified: 2017-05-20 10:33 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Test function does not work correctly (8.20 KB, text/plain)
2010-01-16 23:25 UTC, armando_it
no flags Details
Test cases; identical results as in Excel, check with export to .xls and load there. (7.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-07-16 19:42 UTC, ooo
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description armando_it 2010-01-16 23:22:11 UTC
A1 =  prezzi al:29/12/09 
B1 =  =TEXT(MID(A1;FIND(":";A1)+1;8);"GGGG")
result is #VALUE!

A2 =  prezzi al:29/12/09
B2 =  =MID(A2;FIND(":";A2)+1;8)
C2 =  =TEXT(B2;"GGGG")
result is #VALUE!

A3 =  29/12/09
B3 =  =TEXT(A3;"GGGG")
result is martedì
Comment 1 armando_it 2010-01-16 23:25:25 UTC
Created attachment 67240 [details]
Test  function does not work correctly
Comment 2 Regina Henschel 2010-01-17 13:00:05 UTC
The TEXT function requires a number as first parameter, but the result of the
MID function is a text. So before you can use the TEXT function you have to
convert the result of the MID function to a number. You can use the VALUE or the
DATEVALUE function for converting.

Please try to get help in a forum or mailing list before assuming a bug.
Comment 3 armando_it 2010-01-17 19:14:26 UTC
Many thanks for the quick response. 
You're right, the function VALUE solves the problem and its use is formally
I apologize if I did waste time. 

I did a Google search, but I have not found anything useful. 
The worksheet that generated the error works properly with Excel97.
Version 3.1 of OpenOffice-Calc works properly with or without the function VALUE.
As a end-user, I prefer to write formulas short. And I think it is better that
the computer works for me instead of me for it.
Also, I see difficulties in non-technical users to write complex formulas.
Comment 4 Regina Henschel 2010-01-17 19:46:38 UTC
You are right, it works in OOo3.1.1.

I reopen it, so that a developer can decide, whether it is intended to drop the
automatically conversion or a bug. 
Comment 5 Regina Henschel 2010-01-17 19:49:38 UTC
The formula TEXT(MID(A1;FIND(":";A1)+1;8);"GGGG") works in OOo3.1.1, but no
longer in OOo3.2.
You have to change the language to Italian because of the localized format code.
Comment 6 ooo 2010-01-19 12:12:56 UTC
This not working anymore is a side effect of the new "calculate with strings"
behavior, see
A localized date text is not automatically converted to number anymore.

To get the desired behavior in this case use VALUE() to convert the text to
number, e.g.
Note that this is still locale dependent, with the date argument to VALUE()
being a DD/MM/YYYY string and the Italian number format argument GGGG.
A safer way would be to generate an ISO 8601 string in the form YYYY-MM-DD, e.g.
"2009-12-29", and pass that to VALUE(), maybe let a number format GGGG display
the year instead of using TEXT().

However, a textual argument passed to TEXT() should not result in an error, but
the text be returned instead, unless the number format is a text format, the
same as if a text was displayed using any number format except Text. The
localized date would not be interpreted in any case.
Comment 7 ooo 2010-01-19 12:13:27 UTC
Grabbing issue.
Comment 8 ooo 2010-07-16 19:36:06 UTC
In cws calc56:

changeset 3d7cd0e45986
M formula/inc/formula/errorcodes.hxx
M sc/source/core/tool/interpr1.cxx
M svl/inc/svl/zforlist.hxx
M svl/source/numbers/zforlist.cxx
M svl/source/numbers/zformat.cxx

You can observe the progress and possible integration date of CWS calc56 at

NOTE that this does not change how text is processed if a numeric value is
expected. This only changes how textual arguments to TEXT are handled.
Specifically, for the bug doc, the formula in C2:=TEXT(B2;"GGGG") now returns
the text "12/29/09" as the content of B2 is not convertible to number. This is
identical to the Excel behavior.

Testers please note also that the formula in B3:=TEXT(A3;"GGGG") returns the day
of week only in an Italian locale, in an English locale "ADAD" is returned
because the G format code is used for era and "DDDD" would have to be used to
return the day of week.
Comment 9 ooo 2010-07-16 19:42:10 UTC
Created attachment 70665 [details]
Test cases; identical results as in Excel, check with export to .xls and load there.
Comment 10 ooo 2010-07-19 14:15:16 UTC
Reassigning to QA for verification.
Comment 11 oc 2010-07-26 10:38:17 UTC
verified in internal build cws_calc56