Multiple date formats in one field

[tonypreece]

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?

[BrandonB]

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

[danilang]

hi @tonypreece 

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

[jdunkerley79]

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

[tonypreece]

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!

[BrandonB]

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.

[CampbellRG]

Hi Brandon,

I used the tool, but it converted 9-Jun-63 to 2063-06-09 instead of 1963. Is there any way around this?

[BrandonB]

@CampbellRG  you could follow it up with a formula tool and leverage the datetimeyear(dt) function but the date you provided doesn’t have the first two digits of the year which is why the tool has to infer the date from it. Depending on what logic you want to use you would need to adjust afterwards. For example, if you had 9-Jun-20 would this be 1920 or 2020?

[CampbellRG]

@BrandonB I'm not sure how to use the DTY to fix it. can you give me an example please? Here's a sample of my data

[BrandonB]

For example, you could use a formula tool after this step that is set to update the Date_BBDate column. 

You could say:

IF DateTimeYear(DateTimeNow()) < DateTimeYear([Date_BBDate])
THEN "19"+Right([Date_BBDate],8)
ELSE [Date_BBDate]
ENDIF

This is one potential solution that would take any years past the current year and set them to 19xx