Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Convert a string of numbers to short date

mlanham
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?

 

7 REPLIES 7
benakesh
12 - Quasar

Hi @mlanham ,

 

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

benakesh_0-1573251820330.png

 

mlanham
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!

benakesh
12 - Quasar

@mlanham ,

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

Can you share sample data ?  

mlanham
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

benakesh
12 - Quasar

Hi @mlanham ,

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

benakesh_0-1573490277927.png

 

mlanham
5 - Atom

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

sahartz
8 - Asteroid

Hi,

 

If I also have seconds. How do I present them? I get only the date without the time.

Labels