Excel Upload - handling of Excel dates

[Wei Wang]

We appreciate all the latest Apliqo updates which has providing better Excel validation. One item that still remains a bit of an end-user pain point is when dates are an input field. Since Excel does some automatic conversion for date inputs, date fields that are exported from Apliqo and then edited in Excel, would not upload the correct date.

For example, suppose we have a TM1 string value that stores the date in the “MM/DD/YYYY” format (e.g., 01/01/2023). On exporting the Apliqo screen to Excel, “01/01/2023” is initially treated as a text field in Excel. However If we go in and edit this cell (or add a new row in Excel and type in the date), Excel automatically converts it to the underlying number of “44927”, which is how Excel handles dates. If we later try uploading this back to Apliqo, the correct date string value of “01/01/2023” will not be uploaded.

The only workaround we have found is to manually format the Excel date column as “Text” before typing in the date. However, for most users, this is a step that is difficult to remember.

It would be a good Apliqo enhancement to have some additional logic in the Excel upload module which can convert the Excel dates back to the text strings. This would make the Excel upload experience more straightforward for end users.