Inconsistent Date format (string)

[doublej]

I have a field that contains date values, currently a string field, that has multiple formats that i need to convert to an actual date field.  The current values are "yyyy-mm-dd" and "mm/dd/yyy".  I have tried the DateTime parse tool but with multiple formats this isnt successful.  Anyone run across this?

[JohnJPS]

I would recommend just something like....

IIF(Contains([f1],'-'),[f1],
    DateTimeParse([f1],"%m/$d/%Y"))

[jdunkerley79]

yep - was one of the reasons I build a custom tool: https://github.com/jdunkerley/AlteryxAddIns/releases/tag/v0.3

Easiest work around is a formula tool:

REGEX_Replace([Field1],"(\d\d)/(\d\d)/(\d{4})","$3-$1-$2")

This will convert mm/dd/yyyy to yyyy-mm-dd

There is no need to parse yyyy-mm-dd as Alteryx knows it as a date anyway

[MarqueeCrew · Accepted solution]

@jdunkerley79

beat me to the punch, but here is what I did differently:

IF
Contains([date],"-") THEN date
ELSEIF
Contains([date],"/") THEN Regex_Replace([date],"(\d{1,2})(/)(\d{1,2})(/)(\d{4})","$5-$1-$3")
ELSE
Null()
ENDIF

I setup a conditional set of logic to handle and look at the data (this includes a default of Null).  You could change the default date if you want and if you find that you have another format of data, you can create another ELSEIF statement.

I didn't know that I could not use the (/) group on the delimiter.  Knowing that, I might use a \W instead.  It is a non-word character to look for any delimiter.

Cheers,

Mark

[doublej]

Thanks Marquee, this gave me exaclty what i needed!

[JohnJPS]

Just out of curiosity for you Regex gurus, the following will handle single-digit month and days...

IIF(Contains([f1],'-'),[f1],
    REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$3") + '-' + 
    Right('0' + REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$1"),2) + '-' + 
    Right('0' + REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$2"),2))

But it's pretty ugly... is there a way to modify your original "$3-$1-$2" format to include the '0' padding "Right" approach within Regex output syntax?

[jdunkerley79]

Right(REGEX_Replace(
   "0" + Replace([Field1],"/","/0"),
   "0?(\d{2})/0?(\d{2})/0?(\d{4})","$3-$1-$2"),10)

is my normal trick

Add 0's everywhere then regex it out

[JohnJPS]

That's cool, James.  In the meantime this is what I came up with:

Replace(REGEX_Replace([Field1],"(\d{1,2})/(\d{1,2})/(\d{4})","$3-0$1-0$2"),"-00","-0")

 EDIT: OK, all done simplifying...

[NeilR]

For those who'd prefer to avoid regex at all costs, the Cleanse tool can handle this conversion (with a select tool afterward to convert from string to date).

[MichalK]

Hi Neil,

That's a very useful macro.

I tried modifying it to handle additional date formats (put the formats on the 'Input Formats' List Box and added formulas that handle them) but it does not work. Could you tell me what do I need to do to expand the macro's functionality?

Thanks

Michal