Question

Why do some string values give a correct date time and some not after a Excel data import?

0

Hi guys I am noticing strange behavior when importing date times from xls data import. First of all: 1 - unwanted behaviour that strings with datetimes are converted from MM/dd/yyyy to MM/dd/yy? Why is this happening and how can I set this to yyyy? 2 - The date times in xls excel file are strings, as dd/MM/yyyy. When the raw data is imported with the use of Excel importer, I got a totall new datetime value with 15 year difference. While other string datetimes are taken over correctly to mm-dd-yy. anyone an idea for this strange behavior?

asked 2017-02-02

Rapido

1 answers

Mike Kumpf · Answer 1 · 2017-02-02

Enzo

Are the date columns strings that are imported into String attributes in Mendix? If not, you could take that approach and then run a microflow after import that performs the date conversion in the way you want it to work.

If the date columns in Excel are in fact date values, I have experienced some unanticipated behavior when the Excel files orginate from a Mac. If I recall correctly, Mac Excel dates use a different base date (i.e. Windows Excel uses January 1, 1900 as the base date and all date values in excel are simply the serial number of days since them).

Hope that helps a bit,

Mike