We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

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
BrandonB
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-...
danilang
18 - Pollux
18 - Pollux

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
ACE Emeritus
ACE Emeritus

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
10 - Fireball

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

CampbellRG
7 - Meteor

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

@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
7 - Meteor

@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

CampbellRG_0-1604069121384.png

 

BrandonB
Alteryx
Alteryx

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

date fix.png

Labels