Messy D.O.B. data

Hi everyone

I have a list of c900 records containing date of birth data in a string format. Unfortunately the format of the data is inconsistent and I was wondering how best placed to consistently convert all values into the desired format that the DateTime tool produces? Examples of formats include, but is not exclusive to:

Date of birth

01/04/1995

23-05-1989

1975-12-13

There may be other formats but I'm unable to tell as there are a lot of records.

Can anyone provide the best way to format in DateTime so the desired outcome is a date type field in yyyy-mm-dd?

Thanks

Date Time

Help

Accepted answers

vizAlter

Hi @arundaka02 — Try this logic:

It is based on your 3 sample dates, so you need to add more ELSEIF lines for other date formats (if any)

// for mm/dd/yyyy
IF Contains(Left([Date of birth], 3), "/") THEN 
 DateTimeFormat(DateTimeParse([Date of birth], "%m/%d/%Y"), "%Y-%m-%d") 

// for dd-mm-yyyy
ELSEIF  Contains(Left([Date of birth], 3), "-") THEN 
 DateTimeFormat(DateTimeParse([Date of birth], "%d-%m-%Y"), "%Y-%m-%d") 

ELSE 
 DateTimeFormat(DateTimeParse([Date of birth], "%Y-%m-%d"), "%Y-%m-%d") 
ENDIF

Messy D.O.B. data_v1.yxmd

All comments

BrandonB

Have you given the BB Date tool a try? https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Parse-Dates-Easily-with-BB-Date/ta-p/341682

This is a custom macro that accounts for a variety of different date formats. Otherwise you will need to leverage a formula tool that accounts for the different string types, looks for patterns, and calculates the actual date accordingly.

vizAlter

Hi @arundaka02 — Try this logic:

It is based on your 3 sample dates, so you need to add more ELSEIF lines for other date formats (if any)

// for mm/dd/yyyy
IF Contains(Left([Date of birth], 3), "/") THEN 
 DateTimeFormat(DateTimeParse([Date of birth], "%m/%d/%Y"), "%Y-%m-%d") 

// for dd-mm-yyyy
ELSEIF  Contains(Left([Date of birth], 3), "-") THEN 
 DateTimeFormat(DateTimeParse([Date of birth], "%d-%m-%Y"), "%Y-%m-%d") 

ELSE 
 DateTimeFormat(DateTimeParse([Date of birth], "%Y-%m-%d"), "%Y-%m-%d") 
ENDIF

Messy D.O.B. data_v1.yxmd

vizAlter

@arundaka02, @BrandonB — Thank you!

atcodedog05

Hi @arundaka02 ,

Here is another optimized approach to it.

We can directly rely on the DateTimeParse Function. If the date is not parsed by DateTimeParse it returns a NULL() hence we can use condition on it directly.

See the below formula

IF !IsNull(DateTimeParse([Date of birth], "%m/%d/%Y")) 
THEN DateTimeParse([Date of birth], "%m/%d/%Y")

ELSEIF !IsNull(DateTimeParse([Date of birth], "%d-%m-%Y")) 
THEN DateTimeParse([Date of birth], "%d-%m-%Y")

ELSEIF !IsNull(DateTimeParse([Date of birth], "%d-%m-%Y")) 
THEN DateTimeParse([Date of birth], "%m/%d/%Y")

ELSEIF !IsNull(DateTimeParse([Date of birth], "%Y-%m-%d")) 
THEN DateTimeParse([Date of birth], "%Y-%m-%d")

// Add more date formats

ELSE Null() ENDIF

Output:

Currently the Date format is not in a ladder so its Null(). You can filter these off to find the new Formats that needs to be added.

Hope this helps 🙂

And hopefully there less Date Formats for you to add.

If this post helps you please mark it as solution. And give a like if you dont mind😀👍

Messy Dates.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703