How to convert a v string date from excel to alteryx

[jthompson789]

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?

[Claje]

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.

[jthompson789]

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

[ddiesel]

I have good luck with the DateTime tool under parsing. You can convert Strings to Date/Time format and vice versa.

See attached workflow.

[KaneG]

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?

[jthompson789 · Accepted solution]

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.