I have a case where I have a column with multiple date formats. If you see below the Date Mask or Format is not consistent.
The first row has yyyy-mm-dd
The second row has yyyy/mm/dd
The third row has dd/mm/yyyy
so on and so forth
I have arrived at a crude solution which may hold good if I have only 4 or 5 different date formats in the column to work with. What if I have more date formats. Is there a simpler way to convert multiple date formats in a single column to a standard date format.
Solved! Go to Solution.
You are in the right direction. You can convert into a formula with dateparse like below.
IF REGEX_Match([Date], '(\d{2}/\d{2}/\d{4})') THEN
DateTimeParse([Date],"%d/%m/%Y")
ELSEIF REGEX_Match([Date], '(\d{4}/\d{2}/\d{2})') THEN
DateTimeParse([Date],"%Y/%m/%d")
ELSEIF REGEX_Match([Date], '(\d{4}-\d{2}-\d{2})') THEN
DateTimeParse([Date],"%Y-%m-%d")
ELSEIF REGEX_Match([Date], '(\d{2}-\w+-\d{4})') THEN
DateTimeParse([Date],"%d-%b-%Y")
ELSE
NULL()
ENDIF
Workflow:
Hope this helps : )
Awesome @atcodedog05 . Thank You very much.
Hi @surajmthomas there is also a macro that was created to handle multiple date formats within the same column here is the related post: https://community.alteryx.com/t5/Engine-Works-Blog/Introducing-BB-Date-Date-Parsing-for-the-Rest-of-...
Best of luck
Mo
@surajmthomasfyi - @atcodedog05 's formula is perfect but it is not plausible for regex to know if a two digit code refers to a month or day in the same source - ie, it can't differentiate between December 1, 2021 as "2021-12-01" and January 12th, 2021 as "2021-12-01" and will execute whichever switch comes first. If you do have this kind of variety in your column you'll need an external hint.
@mtouiti Thank you for this. I did have a glance at the BB date macro and I'm very interested in using this and learn about its underlying working. Unfortunately my workplace restricts Downloads from Gallary.
@apathetichell Interesting perspective. Dealing with dates are quite tricky always. Possibilities of such dates coming in are also high considering the fact that the source which captures this date field is not restricted to a particular format. It's a free text field allowing the user to enter the date according to their standard country format. A workaround for this would be great.
I assume the user is not entering the field directly into Alteryx via an app? You'll probably want some check field then before you try to do the convert. My usual hypothesis that a null is greater than a conversion error.