community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Date format

Meteoroid

 

Hi,

 

I got a workflow with an excel input file where is a column with date in this format :

 

 

policytransdate.PNG

so it's basically in ddmmyy format

 

But as you can see, when the day start by a 0 my excel file don't put it.

 

I need Alteryx to recognize those rows as a date so I can convert them to dd/mm/yyyy format like the screenshot below :

 

datedefet.PNG

 

 

Could someone help me on this please.

 

Thanks

 

 

@RomeoCros Assuming you've already changed your field to a string, this formula should convert your input to output:

DateTimeFormat(DateTimeParse(PadLeft([Field1],6,"0"),"%d%m%y"),"%d/%m/%Y")

if Field1 is still a number, you would just enclose it in a tostring() function

Alteryx Certified Partner
Alteryx Certified Partner

First of all...

 

is 20718 20/07/2018 or is it 2/07/2018.

 

I assume the former...

Alteryx Certified Partner
Alteryx Certified Partner

Actually, a more complex example would be...

 

21218

 

is this 21/02/2018 or 02/12/2018, it's impossible to tell? This seems to be one problem associated with your input data. But if you can figure it out then the method provided by @patrick_digan will work a charm...

Meteoroid

No 21218 means 02/12/2018 I will try @patrick_digan method and keep you updated.

 

Thanks for replying

Meteoroid

So I tried your solution and I get this error :

 

String issue.PNG

 

 

Alteryx Partner

You need to select a column to save that result in. Alteryx is saying the column "" is not in the data, because it cannot find the field where you are trying to store the result, which you haven't named yet in the screenshot.

 

Let me know if this helps.

Pulsar

Here is the sample workflow for your case. Hope this is helpful. 

 

workflow.PNGWorkflowResults.PNGResults

Labels