Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Different Date Formats in a Single Column to a Standard Date Format

surajmthomas
8 - Asteroid

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 

 

 

surajmthomas_0-1627494175888.png

 

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.

 

surajmthomas_1-1627494582168.png

 

 

7 REPLIES 7
atcodedog05
22 - Nova
22 - Nova

Hi @surajmthomas 

 

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:

atcodedog05_0-1627495120331.png

 

Hope this helps : )

 

surajmthomas
8 - Asteroid

Awesome @atcodedog05 . Thank You very much.

mtouiti
Alteryx Alumni (Retired)

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

apathetichell
18 - Pollux

@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.

surajmthomas
8 - Asteroid

@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.

surajmthomas
8 - Asteroid

@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. 

apathetichell
18 - Pollux

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.

Labels