Alteryx misinterprets the Dates column in the Excel file. All dates are being imported as 1905-07-**. See the screenshot below for side-by-side. For example, in the Excel file, you have 11/19/2018, but in Designer, you see 1905-07-10.
- Alteryx Designer
- All Versions
File extension is *.xlsx, so the File appears to be an Excel workbook.
Cells data type is Date.
The dates are interpreted as Excel's internal date type (double) on input by Designer. The field type is evaluated and changed by the driver, resulting in these unexpected results.
Suppose that in the Excel file you have date 11/19/2018. The number is being truncated to 2018.11. For date calculations, Excel uses 1899-12-30 as the start date. If you add the values, you get 1905-07-10. Below workflow below, using the Formula tool, demonstrates that calculation:
The formula tool uses the following formula to convert from Excel's internal date to a formatted date:
DateTimeAdd([excel_start_date],[Field1],"days")
The Root Cause is that the File was saved as a "Strict Open XML Spreadsheet" (it has .xlsx extension). If you open the File and do a Save As, you will see:
Re-save the File as "Excel workbook" (also .xlsx extension):
Or you can use a Macro to convert the file from 'Strict Open XML Spreadsheets' (*.xlsx) to 'Excel Workbook (*.xlsx). The Knowledge Base article below contains a Macro that converts .xls files with encoding issues into .xlsx files. You can use that Macro as a Workaround.
How To Input Excel Files That Have Encoding Issues
The Macro runs a command to make the conversion. Since we want to use the Macro to convert the file from (*.xlsx) to (*.xlsx), you will need to make the following changes:
Note: the command line assumes a particular location for the Microsoft command. Your configuration may differ, so you might have to modify it or get some help from your IT group.