I am evaluating dependent cell formula using workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); but while second formula evaluation, POI returns error 15 for cell.getErrorCellValue() Example : Cell A2 contains formula =TEXT(NOW(), "hh:mm:ss") and Cell B2 contains formula =TEXT(TIME(HOUR(A2),MINUTE(A2)+2,SECOND(A2)+2),"hh:mm:ss") In POI, first formula evaluate successfully, but while second formula evaluation it return error cell type.
Could you try POI 3.16 and 3.17-RC1 to see if the issue has been fixed in newer versions?
If the issue still appears in newer POI versions, would you be able to provide a spreadsheet file that demonstrates the issue?
Created attachment 35270 [details] Sample spreadsheet Attached sample spreadsheet for same
Tried with POI 3.16 and 3.17-RC1, but still issue is persist.
(In reply to PJ Fanning from comment #2) > If the issue still appears in newer POI versions, would you be able to > provide a spreadsheet file that demonstrates the issue? Tried with latest version of POI, but still issue is persist. Attached sample spreadsheet for same.
The problem happens because the first cell is a string-value via TEXT(...), but the second cell reads it as time for the HOUR() function, which Excel seems to somehow manage, but Apache POI does not. You might be able to change the first cell to be only "NOW()" and use formatting to get the desired format display, while still keeping the date-value as formula-result.
Fixed in r1855662 The root cause was that OperandResolver#coerceValueToDouble didn't recognize date/time strings and failed for inputs like "12:24" or "2019/01/13". If a string represents date or time Excel converts it to number automatically. The following examples are valid MS Excel formulas: =TEXT(NOW(),"hh:mm:ss") + 0 =TEXT(NOW(),"H:mm AM/PM") + 0 ="2005/01/01" + 0 ="2018/01/01 12:33:54" + 0 Date strings are evaluated as a number of days since January 1, 1900, for example, ="1900/01/01" + 0 evaluates to 1 ="1900/01/02" + 0 evaluates to 2 ="2019/01/19" + 0 evaluates to 43483 Time is evaluated as a decimal number ranging from 0 to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). ="00:00" + 0 evaluates to 0.0 ="12:00" + 0 evaluates to 0.5 ="23:59:59" + 0 evaluates to 0.99988426 Date and time can be used in combination, e.g. ="2019/1/18 3:43:00 PM"+0 evaluates to 43483.65486 The valid separator for date is '/' (slash) and for time is ':' (colon). Any other separators such as '-' result in #VALUE!, for example, ="1900-01-01" + 0 evaluates to #VALUE! This fix also applies to the VALUE function which converts a text string that represents a number to a number . ="2019/1/18 3:43:00 PM"+0 is equivalent to =VALUE("2019/1/18 3:43:00 PM")+0 You can use implicit conversion of VALUE, the result will be the same. Regards, Yegor