cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Discover peer insights or crowdsource your one of a kind Designer question.

How to convert a v string date from excel to alteryx

SOLVED
Meteoroid

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?

Quasar

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.

Meteoroid

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

Highlighted
Bolide

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

 

Capture.PNG

 

Capture.PNG

 

See attached workflow.

Alteryx
Alteryx

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?

Meteoroid

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.