Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Convert a string of numbers to short date

Highlighted
5 - Atom

I have excel general formatting my dates to number strings and I would like to have alteryx convert them to mm/dd/yyyy

 

EX: "43763" transformed to "10/25/2019"...... "43762" transformed to "10/24/2019" etc.

 

Is there a function to do this?

 

Highlighted
12 - Quasar

Hi @mlanham ,

 

Try  DateTimeAdd('1900-01-01',[Field1] - 2 ,'days')  . 

benakesh_0-1573251820330.png

 

Highlighted
5 - Atom

When I try this formula, I receive an error message: "Formula: Invalid type in subtraction operator." 

 

Currently my date column is a V_String and switching it to Int32 or Int64 does not resolve the issue. Any ideas?

 

Additionally, is there a way for the output to be in dd/mm/yyyy format versus "yyyy-mm-dd" your solution displays?

 

Thanks!

Highlighted
12 - Quasar

@mlanham ,

Date format can be changed to dd/mm/yyyy  using datatimeformat()  or  datetime tool. 

Can you share sample data ?  

Highlighted
5 - Atom

Here are screenshots from the select tool to display the type and from the raw data.

 

mlanham_0-1573486705417.png

 

 

mlanham_1-1573486716882.png

 

Thank you for the formatting help there

Highlighted
12 - Quasar

Hi @mlanham ,

The  2nd select tool  changes string to int .  Share your wf if  this  does not help .

benakesh_0-1573490277927.png

 

Highlighted
5 - Atom

Fantastic - this worked! Thank you for all your time on this.

Labels