This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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.
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!
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 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?