Most DataFormatter formatting methods take a 'use1904Windowing' argument to be able to format dates correctly based on whether the original spreadsheet was created using an epoch year of 1900 or 1904. But w/4-part format strings the 'use1904Windowing' argument is ignored and the date is formatted assuming 1900. Workaround: when calling DataFormatter.formatRawCellContents() method you can convert the input number to always use 1900 epoch before passing it in. But this workaround is harder to apply to DataFormatter.formatCellValue(). Here's a test case that illustrates two cases of calling DataFormatter.formatRawCellContents(): a) using a 1-part format string works b) using a 4-part format string ignores 'use1904Windowing' import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.junit.Test; import java.util.Date; import static org.junit.Assert.assertEquals; public class FormatterTest { @Test public void test1904With4PartFormat() { Date date = new Date(); int formatIndex = 105; String formatString1 = "[$-F400]m/d/yy h:mm:ss\\ AM/PM"; String formatString4 = "[$-F400]m/d/yy h:mm:ss\\ AM/PM;[$-F400]m/d/yy h:mm:ss\\ AM/PM;_-* \"\"??_-;_-@_-"; String s1900, s1904; // These two format calls return the same thing, as expected: // The assertEquals() passes with 1-part format s1900 = format(date, formatIndex, formatString1, false); s1904 = format(date, formatIndex, formatString1, true); assertEquals(s1900, s1904); // WORKS // These two format calls should return the same thing but don't: // It fails with 4-part format because the call to CellFormat ignores 'use1904Windowing' s1900 = format(date, formatIndex, formatString4, false); s1904 = format(date, formatIndex, formatString4, true); assertEquals(s1900, s1904); // FAILS // Results in org.junit.ComparisonFailure: // Expected :3/27/19 12:58:34 PM // Actual :3/26/15 12:58:34 PM } private String format(Date date, int formatIndex, String formatString, boolean use1904Windowing) { DataFormatter formatter = new DataFormatter(); double n = DateUtil.getExcelDate(date, use1904Windowing); return formatter.formatRawCellContents(n, formatIndex, formatString, use1904Windowing); } }
Good catch. I found where the use1904Windowing flag was not passed when it should be, and was able to get it or pass it in all calling contexts. Thanks for the test case - it now passes as of r1856648.