Importing date and time fields from Excel files

The Excel import feature now supports importing date and time formats. Excel cells that contain values formatted as date/time, date, or time in the Excel spreadsheet are automatically converted to their respective ISO formats before being stored as form data. This makes them available in your form for viewing, editing, and calculations.

This might sound like an obvious feature, but it turns out that reading the Excel format can be tricky, as Excel doesn't have native date/time data types. Those are stored as floating-point numbers, and only by looking at (and sometimes guessing from) the formatting style for a cell can you really tell that its value contains a date/time. Then the value must be extracted and converted and luckily, there is an algorithm for that. (Oh, and did you know that you cannot represent dates before 1900 in Excel?)

Excel represents many data types using floating-point numbers

In order to implement this enhancement, we had to do quite a bit of research online and in the source code of Apache POI, an open-source library that does a lot of the right things. While we are not using POI directly, we used some of the code and adapted it to our needs.

We hope you will like this feature, which is present in Orbeon Forms 2019.2. See also the documentation.