Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to convert a v string date from excel to alteryx

jthompson789
8 - Asteroid

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?

5 REPLIES 5
Claje
14 - Magnetar

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
8 - Asteroid

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
13 - Pulsar
13 - Pulsar

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.

KaneG
Alteryx Alumni (Retired)

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
8 - Asteroid

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. 

Labels