Troubleshooting: Alteryx Designer Misinterprets Dates from Excel File

[ntobon]

Environment Details

Issue

Alteryx misinterprets Dates reading from 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. 



Environment

• Alteryx Designer
• All Versions

Diagnosis
 
File extension is *.xlsx, so File appears to be an Excel workbook.
Cells data type is Date.

Cause

The dates are being interpreted as Excel internal date types (double) on input by Designer. The field type is evaluated and changed by the driver to produce these unexpected results. 

Suppose that in the Excel file you have date 11/19/2018. What is happening is that that the number is truncated to 2018.11. For date calculations Excel uses 1899-12-30 as the start date. If the values are added you get 1905-07-10.  Below workflow using the Formula tool demonstrates that calculation:




The formula tool uses the following formula to convert from Excel internal date to 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: 

Resolution

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 below Knowledge Base article contains a Macro that converts Excel Files .xls that have Encoding Issues into an .xlsx file. 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.
 

[MikeN]

@alexschmitt and @baohuynhgt - here you go 🙂

[EdP]

If the above doesn't correct the issue it may be that Excel isn't interpreting the fields as dates either.  You can confirm this by setting up data filtering in Excel for the column, clicking the down arrow for the filter, and seeing if the dates appear in the hierarchical selections (Excel sees them as dates) or if they are simply listed as text (they appear in a long list as string do). The article below describes using the Data > Text to Columns feature to correct this.  https://superuser.com/questions/817110/unable-to-get-excel-to-recognise-date-in-column