In MS Excel Version 2016, the following format yields the following example values: yyyy-mm-ddThh:mm:ss => 2019-12-09T00:00:00 yyyy-mm-dd"T"hh:mm:ss => 2019-12-09T00:00:00 yyyy-mm-dd""T""hh:mm:ss => 2019-12-09T00:00:00 yyyy-mm-dd"""T"""hh:mm:ss => 2019-12-09T00:00:00 yyyy-mm-dd "CUSTOM" => 2019-12-09 CUSTOM yyyy-mm-dd"Z" => 2019-12-09Z "yyyy-mm-ddThh:mm:ss" => yyyy-mm-ddThh:mm:ss "yyyy-mm-dd"T"hh:mm:ss" => yyyy-mm-ddThh:mm:ss Only the first one works well for POI. POI does not treat any custom format using double quotes, as date. I believe the fix should be in DataFormatter.createDateFormat(). Current Codes (trunk version @ 5th Dec 2019, line 478+): String formatStr = pFormatStr; formatStr = formatStr.replaceAll("\\\\-","-"); formatStr = formatStr.replaceAll("\\\\,",","); formatStr = formatStr.replaceAll("\\\\\\.","."); // . is a special regexp char formatStr = formatStr.replaceAll("\\\\ "," "); formatStr = formatStr.replaceAll("\\\\/","/"); // weird: m\\/d\\/yyyy formatStr = formatStr.replaceAll(";@", ""); formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy formatStr = formatStr.replace("\"\"", "'"); // replace Excel quoting with Java style quoting formatStr = formatStr.replaceAll("\\\\T","'T'"); // Quote the T is iso8601 style dates Patched Codes: String formatStr = pFormatStr; formatStr = formatStr.replaceAll("\\\\-","-"); formatStr = formatStr.replaceAll("\\\\,",","); formatStr = formatStr.replaceAll("\\\\\\.","."); // . is a special regexp char formatStr = formatStr.replaceAll("\\\\ "," "); formatStr = formatStr.replaceAll("\\\\/","/"); // weird: m\\/d\\/yyyy formatStr = formatStr.replaceAll(";@", ""); formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy formatStr = formatStr.replace("\"", "'"); // ++ replace Excel quoting with Java style quoting, change double to single quotes, char for char. formatStr = formatStr.replaceAll("'+", "'"); // ++ flatten multiple quotes as a single quote (presume user quoted correctly) formatStr = formatStr.replaceAll("\\\\T","'T'"); // Quote the T is iso8601 style dates
The suggested patch is incomplete. DateUtil.isADateFormat(), would also need to be changed, to ignore literals within the custom date/time formats.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65471 fix handles support for the T literal