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.20202020-11-20201120202020-11-202020-11-202020-11-20 How can I transform it?

Hi AngelosPachis, Thank a lot for your prompt response. It is really good and detail explanations. Appreciate it a lot

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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Behavior Analysis

Alias Manager

Accepted answers

All comments

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

example.yxmd

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.

dateformat.yxmd

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.