Created attachment 28080 [details] Class + xlsx file that demonstrates bug described above. If you have a spreadsheet containing date formatting formulas such as: =TEXT(A1, "mmddyyyy") the value displayed using a FormulaEvaluator will be different for the Month than what Excel and cell.getStringCellValue(). For example, if you have "12/16/2011" in A1, the formula above will return "00162011" using a FormulaEvaluator while Excel and cell.getStringCellValue() will return "12162011". Attached is a simple class and example Excel file that demonstrates the issue. Found under java version "1.6.0_24" and "1.7.0" using poi-3.8-beta4
Are you able to try with a few other months? And also try for a cell which has a time in it? (I'm not sure if it's a december specific bug, or if there's confusion between Month and Minute, or something else)
Doesn't matter which month you pick, it does the same thing. I originally tested both with and without a time component, but the time I used had 0 minutes (and I got the same response in both cases). You are probably right about it being a mix-up between month & minute, because if you adjust the formula from: =TEXT(A1, "mmddyyyy") to =TEXT(A1, "MMddyyyy") then the FormulaEvaluator works correctly. Looking at Excel's help (under TEXT function - Guidelines for date and time formats): http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx?CTT=5&origin=HA010277524 it appears that Excel does not use the same formatting as Java's SimpleDateFormat
Looks like we'll need to add some extra logic to handle the differences in cases for month vs minute Are you able to track down (possibly using a debugger) where the code in question is? (That'll speed up the process of fixing it quite a bit!)
I'll try and run it through the debugger sometime this week & post results...
Looks like the issue is probably in: org.apache.poi.ss.formula.functions.TextFunction.TEXT.evaluate() As the javadoc indicates, it's just using SimpleDateFormat: * An implementation of the TEXT function * TEXT returns a number value formatted with the given * number formatting string. This function is not a complete implementation of * the Excel function. This function implements decimal formatting * with the Java class DecimalFormat. For date formatting this function uses * the SimpleDateFormat class.
As of r1221126, the TEXT function delegates the work to DataFormatter, which already has code in it to handle the date formatting rules As part of this, the fraction support in the TEXT function has been pulled out to DataFormatter, which didn't previously support that