Handling Dates Before 1900 (AKA "Year 1900 problem")

[JamoCA]

I'm exporting history-related data from SQL Server, but Excel is only capable of using dates between 1/1/1900 and 12/31/9999.

https://en.wikipedia.org/wiki/Year_1900_problem

Microsoft Excel (using the default 1900 Date System) cannot display dates before the year 1900, although this is not due to a two-digit integer being used to represent the year: Excel uses a floating-point number to store dates and times. The number 1.0 represents the first second of January 1, 1900, in the 1900 Date System (or January 2, 1904, in the 1904 Date System - the default for Macintosh prior to Excel 2016). Numbers smaller than this display as a #VALUE! error

This is how Excel renders pre-1900 dates.

What's the best practice for handling the outputting of these dates using POI? Can it be automated or would a column have to be explicitly set to text with all values pre-transformed? Thanks.

[cfsimplicity]

As you say, Excel (and therefore POI) can't handle pre-1900 date values as dates. So you'll need to either convert your query column to text pre-export, or use the datatypes argument to force the column to string when outputting.

If you need to allow sorting by date, then maybe you could split out the values into separate year, month and day query columns?

[cfsimplicity]

I did consider your suggestion of having the library auto-detect 1900 dates and set them as strings, but unfortunately that would break the library's ability to detect time-only values in ACF, which always include the epoch date 1899-12-30.