CONVERT STRING TO DATE FORMAT

[enkenmendoza]

Hi I have a string data that has different format of date, I want it to convert in a standard date format.

Please refer to the table below:

Original Data (String)	Expected Output (Date)
11.20.2020	2020-11-20
20112020	2020-11-20
2020-11-20	2020-11-20

How can I transform it?

[AngelosPachis]

Hi @enkenmendoza ,

I would use an if statement to look if the original date contains a dot, hyphen or nothing at all; you can also use Regex to check if the date matches a particular pattern. Then with the datetimeparse function you can convert it to a date.

Hope that helps,

Angelos

[SeanAdams]

Hey @enkenmendoza 

as @AngelosPachis  says - the interesting thing here is that you've got multiple different date formats.

The simplest is to use a filter to split your flow into the different date formats (one row per date format) and then use a regex or a date-time format tool - this avoids heavy formulas and makes it easy to maintain over time.

[MarqueeCrew]

@enkenmendoza ,

As @SeanAdams provides you with sets of logic to perform the date hygiene,  the outstanding question of why and how many formats exist remains. As a data investigation and training tool I once generated a macro that would examine data strings and explain the different formats provided. It too is not capable of guessing all possible formats, but will provide you with metrics and formulas that you can use in your workflow. Please dust it off and set if it helps you to learn some data techniques. 

cheers,

 mark

[SH_94]

Hi @ AngelosPachis,

I am interested to know why we didn't put ELSEIF command for the last row , may i know when do we need to put ELSEIF and when do we put only ELSE?

Thank you.

[SH_94]

Hi SeanAdams,

I had encountered the original data date before which contain the following:

1.2020-20-11

2.11-20-2020

May i know how can we use Alteryx to identify if there is any date format as shown as above and update it to the correct date format : 2020-11-20.

Thank you for your time

[AngelosPachis]

Hi @SH_94 ,

The difference between an ELSE and ELSEIF is that the former is the closing statement of an IF function and you will be lead there if no condition from those defined during the IF statement is met.

For example, an IF function should have the following syntax :

IF condition1 then output1 (opening statement with condition1)

ELSEIF condition2 then output2  (subsequent statements with conditions)

ELSEIF condition3 then output3 

..

..

ELSEIF conditionN then outputN 

ELSE (if none of the conditions defined above is met) output

ENDIF

[SH_94]

Hi AngelosPachis,

Thank a lot for your prompt response. It is really good and detail explanations.

Appreciate it a lot