Created attachment 67240 [details]
Test  function does not work correctly

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.

Many thanks for the quick response. 
You're right, the function VALUE solves the problem and its use is formally
correct. 
I apologize if I did waste time. 

However: 
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.

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. 

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.

This not working anymore is a side effect of the new "calculate with strings"
behavior, see http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=307
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.
=TEXT(VALUE(MID(A1;FIND(":";A1)+1;8));"GGGG")
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.

Grabbing issue.

In cws calc56:

changeset 3d7cd0e45986
http://hg.services.openoffice.org/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
http://tools.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fcalc56


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.

Created attachment 70665 [details]
Test cases; identical results as in Excel, check with export to .xls and load there.

Reassigning to QA for verification.

verified in internal build cws_calc56