I'm looking for a way to convert a Excel date field to be read in Alteryx.
Alteryx takes the date of 1/07/2018 and reads it as 1899-12-31.
How to I get Alteryx to read the date as 1/07/2018?
Solved! Go to Solution.
You can use the DateTime tool or the Formula tool.
Here's an example formula:
DATETIMEPARSE([Date],'%m/%d/%Y')
With the DateTime tool, if you choose the option "String to Date/Time format", and choose the "MM/dd/yyyy" format. Either of these should hopefully do what you need.
It didn't work. The Alteryx error said the month number was out of range.
here is an example of the formula:
DateTimeParse([Apply Date],'%m/%d/%Y')
The "Apply Date" is the column I want to convert to a date field that reads 1/7/2018 instead of 1899-12-31
Hi @jthompson789,
If Alteryx is reading the date in as a string of 1/07/2018, then the above methods will work, but if it is being read in as 1899-12-31, then Alteryx is only reading the 1, but as a date. If you put a 1 in a cell in Excel, then convert that cell to a date you will get 1/01/1900 (It's 1899-12-31 in Alteryx because Excel has an error where it incorrectly thinks 1900 is a leap year).
So, If Alteryx is reading it in as 1899-12-31, then it is only reading the 1. Are you able to post an example workbook of this happening?
Thank you for the response. I was given the answer after talking to an Alteryx engineer yesterday. the formal was the DateTimeAdd function using the difference from 1899 to 2017.
The expression was this: DateTimeAdd([Apply Date],43410,"Day")
I hope this helps everyone out their who has had to deal with this using the Excel data format.