Excel Date Input into Alteryx incorrectly

[Intern12]

Good afternoon!

I have seen some similar posts, but nothing that actually solves my problem. I am working with a report that I pull from my companies software. I export the report from the software into excel. This report contains dates. My hope is to not enter into the excel document and change the format of the date in any way. When I don't change the format of the dates in excel, but I then import the excel file into Alteryx, the dates come out looking like "1905-07-10" although it should be 2018-07-30. This is happening directly after using the input tool in Alteryx. Therefore, it is simply not pulling the data correctly/not understanding that it is a date. 

Is there a way to solve this with only making changes in Alteryx? It doesn't seem that Alteryx is pulling this data in any form of pattern, so a formula would not solve the problem I believe. Some form of change on the input seems necessary, but I am not sure what that would look like.

Thank you!

                                   Excel                                                        Alteryx

ExcelAlteryx

[Jay-RDC]

Hello. Try to put a select tool after the input and change the data type to "date".

[DavidSkaife]

Hi @Intern12 

Have you seen this article, seems to match your issue? https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Troubleshooting-Alteryx-Designer-Mi...

[Intern12]

Thank you for the quick response! Unfortunately, I have tried this (I attempted it again just to be sure), but I have not had any luck. It is still showing the same as before.

[Intern12]

Hey @DavidSkaife,

I have seen this article. I did a lot of researching before posting. The article says that the file is not an excel file and by changing it, the problem will be resolved. However, my file is an excel file. It is not a "Strict Open XML Spreadsheet" as is mentioned in the article.

Thanks!

[Jay-RDC]

May I know what's the format of the fields in excel?

[Intern12]

@jayrdelacruz18  

The format in excel is coming through as custom when I export it from my company's software. The format is yyyy-mm-dd. But again, I am not wanting to make any changes in excel. I am wanting to make the changes in Alteryx if possible. That way, when I (or someone else) re-run my report each month, I will not have to go into the excel document to make any changes. I can simply upload it to Alteryx and have everything done nicely.

[DavidSkaife]

Hi @Intern12 

Tricky to know what you have/haven't already researched!

Is the file xlsx or xls? Could you share a sample dataset for us to help troubleshoot further?

[Intern12]

@DavidSkaife 

I understand that! Sadly, my report has some confidential information, and as I have seen in other posts, whenever I go into the excel document to delete the confidential information, the excel document seems to correct itself. When I tried to pull the New* excel document with only the date columns into Alteryx (none of the other confidential info), it came through as it should. That is why I am hoping to have the change occur in Alteryx by somehow manipulating the input tool.

The file is xlsx by the way.

Thank you!

[DavidSkaife]

Hi @Intern12 

No problem, although removing the other fields and leaving just the dates works ok is interesting. You could try troubleshooting it by slowly adding the other fields to see if there is something odd going on?

With the file being xlsx rules out another theory :(

I assume something simple such as the AutoField tool, or performing a DateTime formatting formula doesn't have any affect?