Formula evaluation fails (ErrorCode 15) when using TEXT() function with date

[SDNA-ALP]

NPOI Version

2.7.5

File Type

• XLSX

Upload the Excel File

Test.xlsx

Reproduce Steps

Evaluate cell I2 using IFormulaEvaluator.Evaluate()

Expected

Same result as in Excel

Actual

CellType = Error
ErrorCode = 15
But ICell.StringCellValue returns the correct value

Observation

The issue seems caused by the formula:
TEXT(L2;"dd/mm/yyyy")

If this part is removed, the evaluation works correctly.

Code

        /// <summary></summary>
        private string xReadValueStr_Formula(ISheet Sheet, ICell Cell)
        {
            // Declare the variables
            IFormulaEvaluator mFormulaEvaluator;
            CellValue mCellValue;
            string mResult;

            // Evaluate the formula
            mFormulaEvaluator = Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();

            // Get the cell value
            mCellValue = mFormulaEvaluator.Evaluate(Cell);

            // Set the result
            mResult = mCellValue.CellType switch
            {
                CellType.Numeric => mCellValue.NumberValue.ToString(),
                CellType.String => mCellValue.StringValue,
                CellType.Boolean => mCellValue.BooleanValue.ToString(),
                CellType.Blank => string.Empty,
                CellType.Unknown => string.Empty,
                CellType.Formula => string.Empty,
                CellType.Error => string.Empty, <-- I2 cell returns string.Empty because CelType value is Error -->
                _ => mCellValue.FormatAsString(),
            };

            // Return the result
            return mResult;
        }

[tonyqus]

Why it looks like TEXT(L2;"dd/mm/yyyy")? What's your culture code? is ';' the separater?

[SDNA-ALP]

Yes the semi colon is the separator and the culture code on my computer is French Luxembourg (fr-LU)
L2 column is date format (dd/mm/yyyy hh:mm)

[tonyqus]

I'm afraid ; as separater is not supported so far. At least, I never see a NPOI unit test covers this.

[SDNA-ALP]

OK this is strange because the ; separator is working fine on Excel, I will try to replace it by , separator and I will let you know.

[SDNA-ALP]

Hi @tonyqus, the ; separator is not unsupported, it is the standard and mandatory argument separator in French Luxembourg (fr-LU) locale. Excel enforces this based on Windows regional settings, so this is a perfectly valid formula.

Also worth noting: other basic formulas using ; as separator are working correctly in NPOI (e.g. CONCAT, IF, etc.), so the parser already handles ; in some cases. The issue seems specific to TEXT() function evaluation with dd/mm/yyyy date format.

Could you investigate further?

[FRL-Lux]

Hi @tonyqus,
one way to solve the issue is to replace TEXT(L2;"dd/mm/yyyy") by TEXT(MID(J2; 9;2) & "/" & MID(J2; 6; 2) & "/" & LEFT(J2;4);"dd/mm/yyyy"). With this formula, NPOI returns the correct value.

Could you investigate?

Many thanks