cancel
Showing results for 
Search instead for 
Did you mean: 

How to convert a v string date from excel to alteryx

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
Bolide

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

Highlighted
ddiesel
Asteroid

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?