Resolves: tdf#91453 use configuration of text to number conversion

... also in arithmetic matrix operations.

(combination of 4 commits):

move ConvertStringToValue() implementation from ScInterpreter to ScGlobal

In preparation of matrix calculations to use string conversion
configuration and UI markers for cells containing strings that could be
numeric values.

Change-Id: Ifa9e45853dded249fa741c050ae1f106365f99ea
(cherry picked from commit 329496c1f75f97d2e6119ceb214a2ea1fbadb17a)

add half decoupled ScInterpreter::ConvertStringToValue()

... for back calls of ScMatrix in preparation of tdf#91453

Change-Id: Ife94d1675c1bc7c5611586e3f352ff69264469d7
(cherry picked from commit 6516d5e299bdf0e7aa03d1004763f6d10db48546)

Resolves: tdf#91453 use configuration of text to number conversion

... also in arithmetic matrix operations.

Change-Id: Ia00054d0af383e225d9d40b59da2dc28a817b65a
(cherry picked from commit 466a20ef07f36d50a73a18ab119b3cc18b4babf4)

Resolves: tdf#91453 use configuration of text to number conversion

... also in arithmetic matrix operations if both operands are matrix.

Change-Id: I84609656b166b4e059d9496a5ed732a96e731164
(cherry picked from commit 778d03b59c62d21fd171b81c9fab3ba8496e319d)
Reviewed-on: https://gerrit.libreoffice.org/19172
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Markus Mohrhard <markus.mohrhard@googlemail.com>
Tested-by: Markus Mohrhard <markus.mohrhard@googlemail.com>

6 files changed, 444 insertions, 282 deletions

diff --git a/sc/inc/global.hxx b/sc/inc/global.hxx
index 397808ec6eb2..e84410d781cb 100644
--- a/sc/inc/global.hxx
+++ b/sc/inc/global.hxx
@@ -34,6 +34,7 @@ class ImageList;
 class Bitmap;
 class SfxItemSet;
 class Color;
+struct ScCalcConfig;
 enum class SvtScriptType;
 
 #define SC_COLLATOR_IGNORES ( \
@@ -720,6 +721,117 @@ SC_DLLPUBLIC    static const sal_Unicode* FindUnquoted( const sal_Unicode* pStri
     SC_DLLPUBLIC static OUString    ReplaceOrAppend( const OUString& rString,
                                                      const OUString& rPlaceholder,
                                                      const OUString& rReplacement );
+
+
+    /** Convert string content to numeric value.
+
+        In any case, if rError is set 0.0 is returned.
+
+        If nStringNoValueError is errCellNoValue, that is unconditionally
+        assigned to rError and 0.0 is returned. The caller is expected to
+        handle this situation. Used by the interpreter.
+
+        Usually errNoValue is passed as nStringNoValueError.
+
+        Otherwise, depending on the string conversion configuration different
+        approaches are taken:
+
+
+        For ScCalcConfig::StringConversion::ILLEGAL
+        The error value passed in nStringNoValueError is assigned to rError
+        (and 0.0 returned).
+
+
+        For ScCalcConfig::StringConversion::ZERO
+        A zero value is returned and no error assigned.
+
+
+        For ScCalcConfig::StringConversion::LOCALE
+
+        If the string is empty or consists only of spaces, if "treat empty
+        string as zero" is set 0.0 is returned, else nStringNoValueError
+        assigned to rError (and 0.0 returned).
+
+        Else a non-empty string is passed to the number formatter's scanner to
+        be parsed locale dependent. If that does not detect a numeric value
+        nStringNoValueError is assigned to rError (and 0.0 returned).
+
+        If no number formatter was passed, the conversion falls back to
+        UNAMBIGUOUS.
+
+
+        For ScCalcConfig::StringConversion::UNAMBIGUOUS
+
+        If the string is empty or consists only of spaces, if "treat empty
+        string as zero" is set 0.0 is returned, else nStringNoValueError
+        assigned to rError (and 0.0 returned).
+
+        If the string is not empty the following conversion rules are applied:
+
+        Converted are only integer numbers including exponent, and ISO 8601 dates
+        and times in their extended formats with separators. Anything else,
+        especially fractional numeric values with decimal separators or dates other
+        than ISO 8601 would be locale dependent and is a no-no. Leading and
+        trailing blanks are ignored.
+
+        The following ISO 8601 formats are converted:
+
+        CCYY-MM-DD
+        CCYY-MM-DDThh:mm
+        CCYY-MM-DDThh:mm:ss
+        CCYY-MM-DDThh:mm:ss,s
+        CCYY-MM-DDThh:mm:ss.s
+        hh:mm
+        hh:mm:ss
+        hh:mm:ss,s
+        hh:mm:ss.s
+
+        The century CC may not be omitted and the two-digit year setting is not
+        taken into account. Instead of the T date and time separator exactly one
+        blank may be used.
+
+        If a date is given, it must be a valid Gregorian calendar date. In this
+        case the optional time must be in the range 00:00 to 23:59:59.99999...
+        If only time is given, it may have any value for hours, taking elapsed time
+        into account; minutes and seconds are limited to the value 59 as well.
+
+        If the string can not be converted to a numeric value, the error value
+        passed in nStringNoValueError is assigned to rError.
+
+
+        @param rStr
+            The string to be converted.
+
+        @param rConfig
+            The calculation configuration.
+
+        @param rError
+            Contains the error on return, if any. If an error was set before
+            and the conversion did not result in an error, still 0.0 is
+            returned.
+
+        @param nStringNoValueError
+            The error value to be assigned to rError if string could not be
+            converted to number.
+
+        @param pFormatter
+            The number formatter to use in case of
+            ScCalcConfig::StringConversion::LOCALE. Can but should not be
+            nullptr in which case conversion falls back to
+            ScCalcConfig::StringConversion::UNAMBIGUOUS and if a date is
+            detected the null date is assumed to be the standard 1899-12-30
+            instead of the configured null date.
+
+        @param rCurFmtType
+            Can be assigned a format type in case a date or time or date+time
+            string was converted, e.g. css::util::NumberFormat::DATE or
+            css::util::NumberFormat::TIME or a combination thereof.
+
+     */
+    static double ConvertStringToValue( const OUString& rStr, const ScCalcConfig& rConfig,
+            sal_uInt16 & rError, sal_uInt16 nStringNoValueError,
+            SvNumberFormatter* pFormatter, short & rCurFmtType );
+
 };
 #endif
 
diff --git a/sc/source/core/data/global2.cxx b/sc/source/core/data/global2.cxx
index afafff489423..e3aa0f787743 100644
--- a/sc/source/core/data/global2.cxx
+++ b/sc/source/core/data/global2.cxx
@@ -26,12 +26,15 @@
 #include <stdlib.h>
 #include <ctype.h>
 #include <unotools/syslocale.hxx>
+#include <svl/zforlist.hxx>
+#include <formula/errorcodes.hxx>
 
 #include "global.hxx"
 #include "rangeutl.hxx"
 #include "rechead.hxx"
 #include "compiler.hxx"
 #include "paramisc.hxx"
+#include "calcconfig.hxx"
 
 #include "sc.hrc"
 #include "globstr.hrc"
@@ -357,4 +360,267 @@ OUString ScGlobal::GetDocTabName( const OUString& rFileName,
     return aDocTab;
 }
 
+namespace
+{
+bool isEmptyString( const OUString& rStr )
+{
+    if (rStr.isEmpty())
+        return true;
+    else if (rStr[0] == ' ')
+    {
+        const sal_Unicode* p = rStr.getStr() + 1;
+        const sal_Unicode* const pStop = p - 1 + rStr.getLength();
+        while (p < pStop && *p == ' ')
+            ++p;
+        if (p == pStop)
+            return true;
+    }
+    return false;
+}
+}
+
+double ScGlobal::ConvertStringToValue( const OUString& rStr, const ScCalcConfig& rConfig,
+        sal_uInt16 & rError, sal_uInt16 nStringNoValueError,
+        SvNumberFormatter* pFormatter, short & rCurFmtType )
+{
+    // We keep ScCalcConfig::StringConversion::LOCALE default until
+    // we provide a friendly way to convert string numbers into numbers in the UI.
+
+    double fValue = 0.0;
+    if (nStringNoValueError == errCellNoValue)
+    {
+        // Requested that all strings result in 0, error handled by caller.
+        rError = nStringNoValueError;
+        return fValue;
+    }
+
+    switch (rConfig.meStringConversion)
+    {
+        case ScCalcConfig::StringConversion::ILLEGAL:
+            rError = nStringNoValueError;
+            return fValue;
+        case ScCalcConfig::StringConversion::ZERO:
+            return fValue;
+        case ScCalcConfig::StringConversion::LOCALE:
+            {
+                if (rConfig.mbEmptyStringAsZero)
+                {
+                    // The number scanner does not accept empty strings or strings
+                    // containing only spaces, be on par in these cases with what was
+                    // accepted in OOo and is in AOO (see also the
+                    // StringConversion::UNAMBIGUOUS branch) and convert to 0 to prevent
+                    // interoperability nightmares.
+
+                    if (isEmptyString( rStr))
+                        return fValue;
+                }
+
+                if (!pFormatter)
+                    goto Label_fallback_to_unambiguous;
+
+                sal_uInt32 nFIndex = 0;
+                if (!pFormatter->IsNumberFormat(rStr, nFIndex, fValue))
+                {
+                    rError = nStringNoValueError;
+                    fValue = 0.0;
+                }
+                return fValue;
+            }
+            break;
+        case ScCalcConfig::StringConversion::UNAMBIGUOUS:
+Label_fallback_to_unambiguous:
+            {
+                if (!rConfig.mbEmptyStringAsZero)
+                {
+                    if (isEmptyString( rStr))
+                    {
+                        rError = nStringNoValueError;
+                        return fValue;
+                    }
+                }
+            }
+            // continue below, pulled from switch case for better readability
+            break;
+    }
+
+    OUString aStr( rStr);
+    rtl_math_ConversionStatus eStatus;
+    sal_Int32 nParseEnd;
+    // Decimal and group separator 0 => only integer and possibly exponent,
+    // stops at first non-digit non-sign.
+    fValue = ::rtl::math::stringToDouble( aStr, 0, 0, &eStatus, &nParseEnd);
+    sal_Int32 nLen;
+    if (eStatus == rtl_math_ConversionStatus_Ok && nParseEnd < (nLen = aStr.getLength()))
+    {
+        // Not at string end, check for trailing blanks or switch to date or
+        // time parsing or bail out.
+        const sal_Unicode* const pStart = aStr.getStr();
+        const sal_Unicode* p = pStart + nParseEnd;
+        const sal_Unicode* const pStop = pStart + nLen;
+        switch (*p++)
+        {
+            case ' ':
+                while (p < pStop && *p == ' ')
+                    ++p;
+                if (p < pStop)
+                    rError = nStringNoValueError;
+                break;
+            case '-':
+            case ':':
+                {
+                    bool bDate = (*(p-1) == '-');
+                    enum State { year = 0, month, day, hour, minute, second, fraction, done, blank, stop };
+                    sal_Int32 nUnit[done] = {0,0,0,0,0,0,0};
+                    const sal_Int32 nLimit[done] = {0,12,31,0,59,59,0};
+                    State eState = (bDate ? month : minute);
+                    rCurFmtType = (bDate ? css::util::NumberFormat::DATE : css::util::NumberFormat::TIME);
+                    nUnit[eState-1] = aStr.copy( 0, nParseEnd).toInt32();
+                    const sal_Unicode* pLastStart = p;
+                    // Ensure there's no preceding sign. Negative dates
+                    // currently aren't handled correctly. Also discard
+                    // +CCYY-MM-DD
+                    p = pStart;
+                    while (p < pStop && *p == ' ')
+                        ++p;
+                    if (p < pStop && !rtl::isAsciiDigit(*p))
+                        rError = nStringNoValueError;
+                    p = pLastStart;
+                    while (p < pStop && !rError && eState < blank)
+                    {
+                        if (eState == minute)
+                            rCurFmtType |= css::util::NumberFormat::TIME;
+                        if (rtl::isAsciiDigit(*p))
+                        {
+                            // Maximum 2 digits per unit, except fractions.
+                            if (p - pLastStart >= 2 && eState != fraction)
+                                rError = nStringNoValueError;
+                        }
+                        else if (p > pLastStart)
+                        {
+                            // We had at least one digit.
+                            if (eState < done)
+                            {
+                                nUnit[eState] = aStr.copy( pLastStart - pStart, p - pLastStart).toInt32();
+                                if (nLimit[eState] && nLimit[eState] < nUnit[eState])
+                                    rError = nStringNoValueError;
+                            }
+                            pLastStart = p + 1;     // hypothetical next start
+                            // Delimiters must match, a trailing delimiter
+                            // yields an invalid date/time.
+                            switch (eState)
+                            {
+                                case month:
+                                    // Month must be followed by separator and
+                                    // day, no trailing blanks.
+                                    if (*p != '-' || (p+1 == pStop))
+                                        rError = nStringNoValueError;
+                                    break;
+                                case day:
+                                    if ((*p != 'T' || (p+1 == pStop)) && *p != ' ')
+                                        rError = nStringNoValueError;
+                                    // Take one blank as a valid delimiter
+                                    // between date and time.
+                                    break;
+                                case hour:
+                                    // Hour must be followed by separator and
+                                    // minute, no trailing blanks.
+                                    if (*p != ':' || (p+1 == pStop))
+                                        rError = nStringNoValueError;
+                                    break;
+                                case minute:
+                                    if ((*p != ':' || (p+1 == pStop)) && *p != ' ')
+                                        rError = nStringNoValueError;
+                                    if (*p == ' ')
+                                        eState = done;
+                                    break;
+                                case second:
+                                    if (((*p != ',' && *p != '.') || (p+1 == pStop)) && *p != ' ')
+                                        rError = nStringNoValueError;
+                                    if (*p == ' ')
+                                        eState = done;
+                                    break;
+                                case fraction:
+                                    eState = done;
+                                    break;
+                                case year:
+                                case done:
+                                case blank:
+                                case stop:
+                                    rError = nStringNoValueError;
+                                    break;
+                            }
+                            eState = static_cast<State>(eState + 1);
+                        }
+                        else
+                            rError = nStringNoValueError;
+                        ++p;
+                    }
+                    if (eState == blank)
+                    {
+                        while (p < pStop && *p == ' ')
+                            ++p;
+                        if (p < pStop)
+                            rError = nStringNoValueError;
+                        eState = stop;
+                    }
+
+                    // Month without day, or hour without minute.
+                    if (eState == month || (eState == day && p <= pLastStart) ||
+                            eState == hour || (eState == minute && p <= pLastStart))
+                        rError = nStringNoValueError;
+
+                    if (!rError)
+                    {
+                        // Catch the very last unit at end of string.
+                        if (p > pLastStart && eState < done)
+                        {
+                            nUnit[eState] = aStr.copy( pLastStart - pStart, p - pLastStart).toInt32();
+                            if (nLimit[eState] && nLimit[eState] < nUnit[eState])
+                                rError = nStringNoValueError;
+                        }
+                        if (bDate && nUnit[hour] > 23)
+                            rError = nStringNoValueError;
+                        if (!rError)
+                        {
+                            if (bDate && nUnit[day] == 0)
+                                nUnit[day] = 1;
+                            double fFraction = (nUnit[fraction] <= 0 ? 0.0 :
+                                    ::rtl::math::pow10Exp( nUnit[fraction],
+                                        static_cast<int>( -ceil( log10( static_cast<double>( nUnit[fraction]))))));
+                            if (!bDate)
+                                fValue = 0.0;
+                            else
+                            {
+                                Date aDate(
+                                        sal::static_int_cast<sal_Int16>(nUnit[day]),
+                                        sal::static_int_cast<sal_Int16>(nUnit[month]),
+                                        sal::static_int_cast<sal_Int16>(nUnit[year]));
+                                if (!aDate.IsValidDate())
+                                    rError = nStringNoValueError;
+                                else
+                                {
+                                    if (pFormatter)
+                                        fValue = aDate - *(pFormatter->GetNullDate());
+                                    else
+                                    {
+                                        SAL_WARN("sc.core","ScGlobal::ConvertStringToValue - fixed null date");
+                                        static Date aDefaultNullDate( 30, 12, 1899);
+                                        fValue = aDate - aDefaultNullDate;
+                                    }
+                                }
+                            }
+                            fValue += ((nUnit[hour] * 3600) + (nUnit[minute] * 60) + nUnit[second] + fFraction) / 86400.0;
+                        }
+                    }
+                }
+                break;
+            default:
+                rError = nStringNoValueError;
+        }
+        if (rError)
+            fValue = 0.0;
+    }
+    return fValue;
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index efa11d1147ed..55529acd19bc 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -203,6 +203,11 @@ void ReplaceCell( SCCOL& rCol, SCROW& rRow, SCTAB& rTab );  // for TableOp
 bool IsTableOpInRange( const ScRange& );
 sal_uLong GetCellNumberFormat( const ScAddress& rPos, ScRefCellValue& rCell );
 double ConvertStringToValue( const OUString& );
+public:
+/** For matrix back calls into the current interpreter.
+    Uses rError instead of nGlobalError and rCurFmtType instead of nCurFmtType. */
+double ConvertStringToValue( const OUString&, sal_uInt16& rError, short& rCurFmtType );
+private:
 double GetCellValue( const ScAddress&, ScRefCellValue& rCell );
 double GetCellValueOrZero( const ScAddress&, ScRefCellValue& rCell );
 double GetValueCellValue( const ScAddress&, double fOrig );
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 9d4821c2f7f3..43a22c9181bc 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -185,278 +185,18 @@ sal_uInt16 ScInterpreter::GetCellErrCode( const ScRefCellValue& rCell )
     return rCell.meType == CELLTYPE_FORMULA ? rCell.mpFormula->GetErrCode() : 0;
 }
 
-namespace
-{
-bool isEmptyString( const OUString& rStr )
+double ScInterpreter::ConvertStringToValue( const OUString& rStr )
 {
-    if (rStr.isEmpty())
-        return true;
-    else if (rStr[0] == ' ')
-    {
-        const sal_Unicode* p = rStr.getStr() + 1;
-        const sal_Unicode* const pStop = p - 1 + rStr.getLength();
-        while (p < pStop && *p == ' ')
-            ++p;
-        if (p == pStop)
-            return true;
-    }
-    return false;
-}
+    double fValue = ScGlobal::ConvertStringToValue( rStr, maCalcConfig, nGlobalError, mnStringNoValueError,
+            pFormatter, nCurFmtType);
+    if (nGlobalError)
+        SetError(nGlobalError);
+    return fValue;
 }
 
-/** Convert string content to numeric value.
-
-    Depending on the string conversion configuration different approaches are
-    taken. For ScCalcConfig::StringConversion::UNAMBIGUOUS if the string is not
-    empty the following conversion rules are applied:
-
-    Converted are only integer numbers including exponent, and ISO 8601 dates
-    and times in their extended formats with separators. Anything else,
-    especially fractional numeric values with decimal separators or dates other
-    than ISO 8601 would be locale dependent and is a no-no. Leading and
-    trailing blanks are ignored.
-
-    The following ISO 8601 formats are converted:
-
-    CCYY-MM-DD
-    CCYY-MM-DDThh:mm
-    CCYY-MM-DDThh:mm:ss
-    CCYY-MM-DDThh:mm:ss,s
-    CCYY-MM-DDThh:mm:ss.s
-    hh:mm
-    hh:mm:ss
-    hh:mm:ss,s
-    hh:mm:ss.s
-
-    The century CC may not be omitted and the two-digit year setting is not
-    taken into account. Instead of the T date and time separator exactly one
-    blank may be used.
-
-    If a date is given, it must be a valid Gregorian calendar date. In this
-    case the optional time must be in the range 00:00 to 23:59:59.99999...
-    If only time is given, it may have any value for hours, taking elapsed time
-    into account; minutes and seconds are limited to the value 59 as well.
- */
-
-double ScInterpreter::ConvertStringToValue( const OUString& rStr )
+double ScInterpreter::ConvertStringToValue( const OUString& rStr, sal_uInt16& rError, short& rCurFmtType )
 {
-    // We keep ScCalcConfig::StringConversion::LOCALE default until
-    // we provide a friendly way to convert string numbers into numbers in the UI.
-
-    double fValue = 0.0;
-    if (mnStringNoValueError == errCellNoValue)
-    {
-        // Requested that all strings result in 0, error handled by caller.
-        SetError( mnStringNoValueError);
-        return fValue;
-    }
-
-    switch (maCalcConfig.meStringConversion)
-    {
-        case ScCalcConfig::StringConversion::ILLEGAL:
-            SetError( mnStringNoValueError);
-            return fValue;
-        case ScCalcConfig::StringConversion::ZERO:
-            return fValue;
-        case ScCalcConfig::StringConversion::LOCALE:
-            {
-                if (maCalcConfig.mbEmptyStringAsZero)
-                {
-                    // The number scanner does not accept empty strings or strings
-                    // containing only spaces, be on par in these cases with what was
-                    // accepted in OOo and is in AOO (see also the
-                    // StringConversion::UNAMBIGUOUS branch) and convert to 0 to prevent
-                    // interoperability nightmares.
-
-                    if (isEmptyString( rStr))
-                        return fValue;
-                }
-
-                sal_uInt32 nFIndex = 0;
-                if (!pFormatter->IsNumberFormat(rStr, nFIndex, fValue))
-                {
-                    SetError( mnStringNoValueError);
-                    fValue = 0.0;
-                }
-                return fValue;
-            }
-            break;
-        case ScCalcConfig::StringConversion::UNAMBIGUOUS:
-            {
-                if (!maCalcConfig.mbEmptyStringAsZero)
-                {
-                    if (isEmptyString( rStr))
-                    {
-                        SetError( mnStringNoValueError);
-                        return fValue;
-                    }
-                }
-            }
-            // continue below, pulled from switch case for better readability
-            break;
-    }
-
-    OUString aStr( rStr);
-    rtl_math_ConversionStatus eStatus;
-    sal_Int32 nParseEnd;
-    // Decimal and group separator 0 => only integer and possibly exponent,
-    // stops at first non-digit non-sign.
-    fValue = ::rtl::math::stringToDouble( aStr, 0, 0, &eStatus, &nParseEnd);
-    sal_Int32 nLen;
-    if (eStatus == rtl_math_ConversionStatus_Ok && nParseEnd < (nLen = aStr.getLength()))
-    {
-        // Not at string end, check for trailing blanks or switch to date or
-        // time parsing or bail out.
-        const sal_Unicode* const pStart = aStr.getStr();
-        const sal_Unicode* p = pStart + nParseEnd;
-        const sal_Unicode* const pStop = pStart + nLen;
-        switch (*p++)
-        {
-            case ' ':
-                while (p < pStop && *p == ' ')
-                    ++p;
-                if (p < pStop)
-                    SetError( mnStringNoValueError);
-                break;
-            case '-':
-            case ':':
-                {
-                    bool bDate = (*(p-1) == '-');
-                    enum State { year = 0, month, day, hour, minute, second, fraction, done, blank, stop };
-                    sal_Int32 nUnit[done] = {0,0,0,0,0,0,0};
-                    const sal_Int32 nLimit[done] = {0,12,31,0,59,59,0};
-                    State eState = (bDate ? month : minute);
-                    nCurFmtType = (bDate ? css::util::NumberFormat::DATE : css::util::NumberFormat::TIME);
-                    nUnit[eState-1] = aStr.copy( 0, nParseEnd).toInt32();
-                    const sal_Unicode* pLastStart = p;
-                    // Ensure there's no preceding sign. Negative dates
-                    // currently aren't handled correctly. Also discard
-                    // +CCYY-MM-DD
-                    p = pStart;
-                    while (p < pStop && *p == ' ')
-                        ++p;
-                    if (p < pStop && !rtl::isAsciiDigit(*p))
-                        SetError( mnStringNoValueError);
-                    p = pLastStart;
-                    while (p < pStop && !nGlobalError && eState < blank)
-                    {
-                        if (eState == minute)
-                            nCurFmtType |= css::util::NumberFormat::TIME;
-                        if (rtl::isAsciiDigit(*p))
-                        {
-                            // Maximum 2 digits per unit, except fractions.
-                            if (p - pLastStart >= 2 && eState != fraction)
-                                SetError( mnStringNoValueError);
-                        }
-                        else if (p > pLastStart)
-                        {
-                            // We had at least one digit.
-                            if (eState < done)
-                            {
-                                nUnit[eState] = aStr.copy( pLastStart - pStart, p - pLastStart).toInt32();
-                                if (nLimit[eState] && nLimit[eState] < nUnit[eState])
-                                    SetError( mnStringNoValueError);
-                            }
-                            pLastStart = p + 1;     // hypothetical next start
-                            // Delimiters must match, a trailing delimiter
-                            // yields an invalid date/time.
-                            switch (eState)
-                            {
-                                case month:
-                                    // Month must be followed by separator and
-                                    // day, no trailing blanks.
-                                    if (*p != '-' || (p+1 == pStop))
-                                        SetError( mnStringNoValueError);
-                                    break;
-                                case day:
-                                    if ((*p != 'T' || (p+1 == pStop)) && *p != ' ')
-                                        SetError( mnStringNoValueError);
-                                    // Take one blank as a valid delimiter
-                                    // between date and time.
-                                    break;
-                                case hour:
-                                    // Hour must be followed by separator and
-                                    // minute, no trailing blanks.
-                                    if (*p != ':' || (p+1 == pStop))
-                                        SetError( mnStringNoValueError);
-                                    break;
-                                case minute:
-                                    if ((*p != ':' || (p+1 == pStop)) && *p != ' ')
-                                        SetError( mnStringNoValueError);
-                                    if (*p == ' ')
-                                        eState = done;
-                                    break;
-                                case second:
-                                    if (((*p != ',' && *p != '.') || (p+1 == pStop)) && *p != ' ')
-                                        SetError( mnStringNoValueError);
-                                    if (*p == ' ')
-                                        eState = done;
-                                    break;
-                                case fraction:
-                                    eState = done;
-                                    break;
-                                case year:
-                                case done:
-                                case blank:
-                                case stop:
-                                    SetError( mnStringNoValueError);
-                                    break;
-                            }
-                            eState = static_cast<State>(eState + 1);
-                        }
-                        else
-                            SetError( mnStringNoValueError);
-                        ++p;
-                    }
-                    if (eState == blank)
-                    {
-                        while (p < pStop && *p == ' ')
-                            ++p;
-                        if (p < pStop)
-                            SetError( mnStringNoValueError);
-                        eState = stop;
-                    }
-
-                    // Month without day, or hour without minute.
-                    if (eState == month || (eState == day && p <= pLastStart) ||
-                            eState == hour || (eState == minute && p <= pLastStart))
-                        SetError( mnStringNoValueError);
-
-                    if (!nGlobalError)
-                    {
-                        // Catch the very last unit at end of string.
-                        if (p > pLastStart && eState < done)
-                        {
-                            nUnit[eState] = aStr.copy( pLastStart - pStart, p - pLastStart).toInt32();
-                            if (nLimit[eState] && nLimit[eState] < nUnit[eState])
-                                SetError( mnStringNoValueError);
-                        }
-                        if (bDate && nUnit[hour] > 23)
-                            SetError( mnStringNoValueError);
-                        if (!nGlobalError)
-                        {
-                            if (bDate && nUnit[day] == 0)
-                                nUnit[day] = 1;
-                            double fFraction = (nUnit[fraction] <= 0 ? 0.0 :
-                                    ::rtl::math::pow10Exp( nUnit[fraction],
-                                        static_cast<int>( -ceil( log10( static_cast<double>( nUnit[fraction]))))));
-                            fValue = (bDate ? GetDateSerial(
-                                        sal::static_int_cast<sal_Int16>(nUnit[year]),
-                                        sal::static_int_cast<sal_Int16>(nUnit[month]),
-                                        sal::static_int_cast<sal_Int16>(nUnit[day]),
-                                        true, false) : 0.0);
-                            fValue += ((nUnit[hour] * 3600) + (nUnit[minute] * 60) + nUnit[second] + fFraction) / 86400.0;
-                        }
-                    }
-                }
-                break;
-            default:
-                SetError( mnStringNoValueError);
-        }
-        if (nGlobalError)
-            fValue = 0.0;
-    }
-    return fValue;
+    return ScGlobal::ConvertStringToValue( rStr, maCalcConfig, rError, mnStringNoValueError, pFormatter, rCurFmtType);
 }
 
 double ScInterpreter::GetCellValue( const ScAddress& rPos, ScRefCellValue& rCell )
diff --git a/sc/source/core/tool/interpr5.cxx b/sc/source/core/tool/interpr5.cxx
index 6616c2db0aea..bfaa438d8c81 100644
--- a/sc/source/core/tool/interpr5.cxx
+++ b/sc/source/core/tool/interpr5.cxx
@@ -1110,8 +1110,10 @@ static ScMatrixRef lcl_MatrixCalculation(
         {
             for (j = 0; j < nMinR; j++)
             {
+                bool bVal1 = rMat1.IsValueOrEmpty(i,j);
+                bool bVal2 = rMat2.IsValueOrEmpty(i,j);
                 sal_uInt16 nErr;
-                if (rMat1.IsValueOrEmpty(i,j) && rMat2.IsValueOrEmpty(i,j))
+                if (bVal1 && bVal2)
                 {
                     double d = Op(rMat1.GetDouble(i,j), rMat2.GetDouble(i,j));
                     xResMat->PutDouble( d, i, j);
@@ -1121,6 +1123,28 @@ static ScMatrixRef lcl_MatrixCalculation(
                 {
                     xResMat->PutError( nErr, i, j);
                 }
+                else if ((!bVal1 && rMat1.IsString(i,j)) || (!bVal2 && rMat2.IsString(i,j)))
+                {
+                    sal_uInt16 nError1 = 0;
+                    short nFmt1 = 0;
+                    double fVal1 = (bVal1 ? rMat1.GetDouble(i,j) :
+                            pInterpreter->ConvertStringToValue( rMat1.GetString(i,j).getString(), nError1, nFmt1));
+
+                    sal_uInt16 nError2 = 0;
+                    short nFmt2 = 0;
+                    double fVal2 = (bVal2 ? rMat2.GetDouble(i,j) :
+                            pInterpreter->ConvertStringToValue( rMat2.GetString(i,j).getString(), nError2, nFmt2));
+
+                    if (nError1)
+                        xResMat->PutError( nError1, i, j);
+                    else if (nError2)
+                        xResMat->PutError( nError2, i, j);
+                    else
+                    {
+                        double d = Op( fVal1, fVal2);
+                        xResMat->PutDouble( d, i, j);
+                    }
+                }
                 else
                     xResMat->PutError( errNoValue, i, j);
             }
diff --git a/sc/source/core/tool/scmatrix.cxx b/sc/source/core/tool/scmatrix.cxx
index 5ef8378891d8..c5c42f338ba5 100644
--- a/sc/source/core/tool/scmatrix.cxx
+++ b/sc/source/core/tool/scmatrix.cxx
@@ -2560,7 +2560,10 @@ struct COp<T, double>
 };
 
 /** A template for operations where operands are supposed to be numeric.
-    A non-numeric (string) operand leads to an errNoValue DoubleError.
+    A non-numeric (string) operand leads to the configured conversion to number
+    method being called if in interpreter context and an errNoValue DoubleError
+    if conversion was not possible, else to an unconditional errNoValue
+    DoubleError.
     An empty operand evaluates to 0.
     XXX: semantically TEmptyRes and types other than number_value_type are
     unused, but this template could serve as a basis for future enhancements.
@@ -2570,6 +2573,7 @@ struct MatOp
 {
 private:
     TOp maOp;
+    ScInterpreter* mpErrorInterpreter;
     svl::SharedString maString;
     double mfVal;
     COp<TOp, TEmptyRes> maCOp;
@@ -2579,8 +2583,10 @@ public:
     typedef TRet number_value_type;
     typedef svl::SharedString string_value_type;
 
-    MatOp( TOp aOp, double fVal = 0.0, const svl::SharedString& rString = svl::SharedString() ):
+    MatOp( TOp aOp, ScInterpreter* pErrorInterpreter,
+            double fVal = 0.0, const svl::SharedString& rString = svl::SharedString() ):
         maOp(aOp),
+        mpErrorInterpreter(pErrorInterpreter),
         maString(rString),
         mfVal(fVal)
     { }
@@ -2595,8 +2601,17 @@ public:
         return maOp((double)bVal, mfVal);
     }
 
-    double operator()(const svl::SharedString&) const
+    double operator()(const svl::SharedString& rStr) const
     {
+        if (mpErrorInterpreter)
+        {
+            sal_uInt16 nError = 0;
+            short nCurFmtType = 0;
+            double fValue = mpErrorInterpreter->ConvertStringToValue( rStr.getString(), nError, nCurFmtType);
+            if (nError)
+                return CreateDoubleError( nError);
+            return fValue;
+        }
         return CreateDoubleError( errNoValue);
     }
 
@@ -2616,21 +2631,21 @@ public:
 void ScMatrix::NotOp( ScMatrix& rMat)
 {
     auto not_ = [](double a, double){return double(a == 0.0);};
-    matop::MatOp<decltype(not_), double> aOp(not_);
+    matop::MatOp<decltype(not_), double> aOp(not_, pImpl->GetErrorInterpreter());
     pImpl->ApplyOperation(aOp, *rMat.pImpl);
 }
 
 void ScMatrix::NegOp( ScMatrix& rMat)
 {
     auto neg_ = [](double a, double){return -a;};
-    matop::MatOp<decltype(neg_), double> aOp(neg_);
+    matop::MatOp<decltype(neg_), double> aOp(neg_, pImpl->GetErrorInterpreter());
     pImpl->ApplyOperation(aOp, *rMat.pImpl);
 }
 
 void ScMatrix::AddOp( double fVal, ScMatrix& rMat)
 {
     auto add_ = [](double a, double b){return a + b;};
-    matop::MatOp<decltype(add_)> aOp(add_, fVal);
+    matop::MatOp<decltype(add_)> aOp(add_, pImpl->GetErrorInterpreter(), fVal);
     pImpl->ApplyOperation(aOp, *rMat.pImpl);
 }
 
@@ -2639,13 +2654,13 @@ void ScMatrix::SubOp( bool bFlag, double fVal, ScMatrix& rMat)
     if (bFlag)
     {
         auto sub_ = [](double a, double b){return b - a;};
-        matop::MatOp<decltype(sub_)> aOp(sub_, fVal);
+        matop::MatOp<decltype(sub_)> aOp(sub_, pImpl->GetErrorInterpreter(), fVal);
         pImpl->ApplyOperation(aOp, *rMat.pImpl);
     }
     else
     {
         auto sub_ = [](double a, double b){return a - b;};
-        matop::MatOp<decltype(sub_)> aOp(sub_, fVal);
+        matop::MatOp<decltype(sub_)> aOp(sub_, pImpl->GetErrorInterpreter(), fVal);
         pImpl->ApplyOperation(aOp, *rMat.pImpl);
     }
 }
@@ -2653,7 +2668,7 @@ void ScMatrix::SubOp( bool bFlag, double fVal, ScMatrix& rMat)
 void ScMatrix::MulOp( double fVal, ScMatrix& rMat)
 {
     auto mul_ = [](double a, double b){return a * b;};
-    matop::MatOp<decltype(mul_)> aOp(mul_, fVal);
+    matop::MatOp<decltype(mul_)> aOp(mul_, pImpl->GetErrorInterpreter(), fVal);
     pImpl->ApplyOperation(aOp, *rMat.pImpl);
 }
 
@@ -2662,13 +2677,13 @@ void ScMatrix::DivOp( bool bFlag, double fVal, ScMatrix& rMat)
     if (bFlag)
     {
         auto div_ = [](double a, double b){return sc::div(b, a);};
-        matop::MatOp<decltype(div_)> aOp(div_, fVal);
+        matop::MatOp<decltype(div_)> aOp(div_, pImpl->GetErrorInterpreter(), fVal);
         pImpl->ApplyOperation(aOp, *rMat.pImpl);
     }
     else
     {
         auto div_ = [](double a, double b){return sc::div(a, b);};
-        matop::MatOp<decltype(div_)> aOp(div_, fVal);
+        matop::MatOp<decltype(div_)> aOp(div_, pImpl->GetErrorInterpreter(), fVal);
         pImpl->ApplyOperation(aOp, *rMat.pImpl);
     }
 }
@@ -2678,13 +2693,13 @@ void ScMatrix::PowOp( bool bFlag, double fVal, ScMatrix& rMat)
     if (bFlag)
     {
         auto pow_ = [](double a, double b){return pow(b, a);};
-        matop::MatOp<decltype(pow_)> aOp(pow_, fVal);
+        matop::MatOp<decltype(pow_)> aOp(pow_, pImpl->GetErrorInterpreter(), fVal);
         pImpl->ApplyOperation(aOp, *rMat.pImpl);
     }
     else
     {
         auto pow_ = [](double a, double b){return pow(a, b);};
-        matop::MatOp<decltype(pow_)> aOp(pow_, fVal);
+        matop::MatOp<decltype(pow_)> aOp(pow_, pImpl->GetErrorInterpreter(), fVal);
         pImpl->ApplyOperation(aOp, *rMat.pImpl);
     }
 }