Created attachment 39052 [details] Comparing POI and Excel results of TEXT function for certain corner cases Please see the attached workbook where the TEXT function results for POI and Excel for various corner cases are compared. The differences are highlighted with the red text. I have some changes done to the TEXT function which would fix those cases highlighted in yellow. It would also fix the 53397 issue as it is essentially covered by the row 5. Please let me know if it is wanted. If so I will create a patch but first I'd create corresponding test cases (probably in the existing TestText class). I can look at other cases as well but need some guideline. First they are a bit more complicated as the fix may not be isolated just to the TEXT function implementation. Second some of them may not be desired, for example: - Latest date allowed for calculation in excel is December 31, 9999 as per this: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 The row 10 shows that POI can handle dates beyond and doesn't report an error. Is it correct? - Dates prior March 1 1900 are incorrect in Excel due to: https://learn.microsoft.com/en-US/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year Does POI want to replicate this incorrect dates? So question would be what of these not yellow cases should be fixed.
Created attachment 39086 [details] A patch fixing corner cases mark as yellow, adding unit tests Please see the attached patch for the TEXT function corner cases marked as yellow in the attached workbook as per my previous comment. The patch also adds corresponding test cases to the ss.formula.functions.TestText.java class.
applied with r1912973