community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Multiple date formats in one field

Atom

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?

 

Alteryx
Alteryx
The BBdate tool may help take care of a lot of the mixed formatting issues you will run into, but there may still be a couple of the rules you mentioned that it won’t take care of. Give this a try and let me know what you think!

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Parse-Dates-Easily-with-BB-Date/ta-...
Nebula

hi @tonyp 

 

The short answer is "no, there is no magic formula." Edit: The BBDate tool comes close. thanks @BrandonB 

 

The more useful answer is there are strategies you can use to make your task easier.  You initial formula technique is a good start to get all the delimiters as "-".  From there you need to start dealing with specific cases

1. !contains([date],"-").   Use a filter to pull these out treat them according to length

    a. length =8  "$d$m$Y"

    b length =6  "$d$m$y"

    c. others  see point 2 c below

2. anything that isn't length=10. 

    a. if it's 8, then it's probably a 2 digit year, so deal with those. 

    b any other length will have to be dealt with almost case by case.  i.e. 219 is probably 2019, but is 200, 2001 2009, 2010.

 

If you're doing statistical analysis on the data, you may just be able to exclude the dates that are ambiguous, depending on quantity.

 

Good Luck   

 

Dan

For the specific set posed:

 

 

DateTimeParse(REGEX_Replace([Date], '[/\-]',''),"%d%m%y")

 

comes very close.

 

Basically remove the separator then rely on Alteryx DateTimeParse which is indifferent on 2/4 digit years

 

It cant do the 3 digit years as no way to guess the mistake! If you can provide a rule then easy to expand above to work. E.g. if you wanted to add a 2 something like:

 

DateTimeParse(REGEX_Replace(REGEX_Replace([Date], '[/\-]',''),"^(\d{4})(\d{3})$","$12$2"),"%d%m%y")

 

would work

 

Sample attached

Atom

Hi Brandon. The BB Date tool looks great. Unfortunately it's hosted in dropbox which my corporate IT have blocked and even if I get it from home and email it in, I won't be able to install it without a month worth of hurdles!

Alteryx
Alteryx

Attached! Just download this file and change the .zip extension to .yxi, and then double click it to install. I think you should still be able to do it because it is an Alteryx file and a bit different than a standard software installation.

Labels