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

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.

Different_Date_Format_In_Single_Column.yxmd

Input

Regex

Parse

Preparation

Accepted answers

atcodedog05

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:

Hope this helps : )

Different_Date_Format_In_Single_Column.yxmd

All comments

atcodedog05

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:

Hope this helps : )

Different_Date_Format_In_Single_Column.yxmd

surajmthomas

Awesome @atcodedog05 . Thank You very much.

mtouiti

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-Us/ba-p/24370

Best of luck

apathetichell

@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

@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

@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

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317