Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Multiple date formats in one field

tonypreece
10 - Fireball

Hi folks,

 

I've received a csv file where the date filed contains multiple different date formats: dd/mm/yyyy, dd/mm/yy, dd-mm-yyyy, ddmmyyyy. Also silly errors like dd/mm/yyy.

 

I've resolved most of the errors but I'm looking for a way to fix the rest handful of rows where the date is either:

dd/mmyyyy

dd/mm/yyy (digit missing from the year)

 

Can anybody recommend a nice way to find and fix these fields?

 

14 REPLIES 14
CampbellRG
7 - Meteor

@BrandonB 

Awesome.

Thank you!

BrandonB
Alteryx
Alteryx

You will need to be cautious of situations like 9-Jun-20 though because if you ran that today it would use 2020 and it isn't clear how you decide which one to use when the year is not provided. 

kimikoxavier
5 - Atom

@jdunkerley75 If it's not too much could you please explain what the meaning of [/\-] is in the formula?

CampbellRG
7 - Meteor

@kimikoxavier That's just telling which punctuation to remove from the data, so it will remove / or \ or - if it's in the date.

kimikoxavier
5 - Atom

Got it. Thanks so much!

Labels