improper format of dates while importing using excel importer

when we importing dates from excel file to application using excel importer, it is importing dates in a improper formats for example 11/12/0017, 12:00 AM the dates in the excel file are in following format 14-11-14 10:31:13 so how to import dates properly?
2 answers

We are currently working/testing a new release of the excel importer module with extended capabilities to parse data from excel.

The current appstore version always loads the raw data, in case of a date some decimal number that indicates what the actual date is.
This can obviously can cause trouble, and when importing a value to string attribute it almost always looks different than what you saw in excel.

The new version will use the display mask of the field to identify the value, and use the display mask to evaluate the value as well.
This means that if you import any value to a string it will always use the display mask, and import exactly what the user sees.
In case of dates, the display mask is utilized to determine how the date is being shown (localized or in a static timezone), and will use this to localize the date according to what you have configured in the application.

If your situation is cause because of an exotic way of storing the value, it might be solved. However in my experience the date has never been 1997 years off. You might want to validate the value in excel, even if it shows '14 what is the prefix? If it says 0014 in excel it would explain these miscalculations.
Or please validate how the date is processed in the application.


A couple of thoughts (while we are waiting for the new version Jasper described, which sounds like a great improvement!).

  • AFAIK, dates in Excel are stored as an integer. This integer represents the elapsed time since an arbitrary starting date/time (lets say midnight January 1, 1970). I have never had issues with importing spreadsheets that were created/originated from Excel for Windows. However, I did have a weird issue with dates that from an Excel file that originated on Mac. When troubleshooting, I discovered that Excel for Mac uses a different starting date/time than Excel for Windows. Copying from the Mac origin Excel file and pasting values only into a Windows origin Excel file corrected that issue.
  • If that doesn't work, you could create an additional column in your Excel file, write an Excel formula that parses the date into a string in your new column, copy and paste values only into another Excel worksheet, import the values only worksheet into a temporary entity in Mendix and use parse datetime functions in Mendix to get the dates out of your string column. Its a long workaround, but an option.....