Complex date converison

Hi everyone,

I have a date conversion question. I am struggling since yesterday already to deal with this problem. My dates are in verious formats like:

2009-1-24

01/24/2009
05-24-2015
24-01-2015
march-2019
Feb,2020
January-16th-2020
2019-March
2019, May

How can I bring them all into a same format e.g (yyyy-mm-dd) ? I went through many date time related posts but never found any post dealing with this complexity. Could you guys please help me out here?

Datasets

Date Time

Data Investigation

Accepted answers

atcodedog05

Hi @wtahir

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

convert them all.yxmd

All comments

atcodedog05

Hi @wtahir

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

convert them all.yxmd

jamielaird

Hi @wtahir,

Here's one possible solution:

1) Add a column to your dataset that defines the format of the date e.g. YYYY-MM-DD, MM/DD/YYYY etc. This will require some manual work, so if you have lots of records you might want to use the formula tool to add some checks that will help you, e.g. counting the number of slashes and hypens and the length of the string and then sorting by these fields to group similar dates.

2) Use the Filter tool to seperate each group of records with a certain date format into a different stream

3) Add a DateTime tool and configure it appropriately for each given date type. You'll find lots of information and guidance on this tool here: https://help.alteryx.com/current/designer/datetime-tool

There might be APIs or Python libraries out there that can handle this in a more automated fashion, so if you have a massive volume of data or expect to need to do this multiple times you might want to explore that avenue.

jamielaird

Well there you go - you got a generalized solution from me and a specific solution from @atcodedog05, and if you combine the two I think you'll be well on your way to solving this. Let us know how you get on.

wtahir

@atcodedog05, your solution works perfect. Highly appreciate your help and ofcourse you get a like from me 🙂

wtahir

@jamielaird thank you for your useful advise. I would surely have to look into it. I am totally new to alteryx but have worked with python before so I was trying to translate some regex ideas combined with python's dataframe but nothing was working and @atcodedog05 made my day. Truly appreciate this great help from this wonderful community

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703